Creating an Excel Matrix

Hi Tisino;

I started to create a mockup that only allowed one selection for the first four and two selections for the second but I was using macros and realized somewhat belatedly that macro-enabled workbooks are not always well received in a general distribution.

So I finished that one and converted the functionality over to another non-macro based workbook as best as I could using more complicated formulas and a fair bit of conditional formatting rules. In this macro-free workbook, multiple selections (e.g. X's) can be made but are not displayed and their prices are not recorded. If a second X is applied to the Entree group, only the first one (from the top down) is displayed. The second one remains an invisible X and if the first one is cleared then the second becomes visible and its pricing replaces the first.

On both sheets, I've used named ranges for the five serving categories to tighten up code and formulas. Look in View, Name Manager for more details on these. These would also facilitate adding more meal items to the groups by adjusting automatically to an inserted row.

I'll let you have a look and decide which is a better fit for your purpose. You will have to download them for full functionality.

Reservation_v2 (CF rules no macros).xlsx

Reservation_v2 (selection names and macros).xlsm

You are going to require a VBA routine that recursively loops through all combinations.

I wrote the following sample routine and helper function to achieve the above.

Sub mcr_Build_Meal()

Dim e As Long, rw As Long, s As Long, sp As Long, d As Long, b As Long

Dim p As Double, vr As Variant, vs As Variant, app As Application

Set app = Application

vs = Array("Entrée", "Starch", "Soup", "Dessert", "Beverage")

vr = Array(vbNullString, vbNullString, vbNullString, vbNullString, vbNullString)

With ActiveSheet

.Cells(1, 5).CurrentRegion.Offset(1, 0).ClearContents

For e = 1 To app.CountIf(.Columns(3), vs(0))

vr(0) = fcnGet_Food_from_Serving_and_Pos(CStr(vs(0)), e)

For s = 1 To app.CountIf(.Columns(3), vs(1))

vr(1) = fcnGet_Food_from_Serving_and_Pos(CStr(vs(1)), s)

For sp = 1 To app.CountIf(.Columns(3), vs(2))

vr(2) = fcnGet_Food_from_Serving_and_Pos(CStr(vs(2)), sp)

For d = 1 To app.CountIf(.Columns(3), vs(3))

vr(3) = fcnGet_Food_from_Serving_and_Pos(CStr(vs(3)), d)

For b = 1 To app.CountIf(.Columns(3), vs(4))

vr(4) = fcnGet_Food_from_Serving_and_Pos(CStr(vs(4)), b)

rw = .Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row

.Cells(rw, 5).Resize(1, 5) = vr

.Cells(rw, 10) = _

app.SumIfs(.Columns(2), .Columns(1), .Cells(rw, 5).Value, .Columns(3), vs(0)) + _

app.SumIfs(.Columns(2), .Columns(1), .Cells(rw, 6).Value, .Columns(3), vs(1)) + _

app.SumIfs(.Columns(2), .Columns(1), .Cells(rw, 7).Value, .Columns(3), vs(2)) + _

app.SumIfs(.Columns(2), .Columns(1), .Cells(rw, 8).Value, .Columns(3), vs(3)) + _

app.SumIfs(.Columns(2), .Columns(1), .Cells(rw, 9).Value, .Columns(3), vs(4))

Next b

Next d

Next sp

Next s

Next e

End With

Set app = Nothing

End Sub

Private Function fcnGet_Food_from_Serving_and_Pos(sFd As String, iPos As Long) As String

Dim f As Long, tmp As String, fr As Range

With ActiveSheet.Cells(1, 1).CurrentRegion

Set fr = .Cells(1, 3)

For f = 1 To iPos

Set fr = .Columns(3).Find(What:=sFd, After:=fr, LookIn:=xlValues, LookAt:=xlPart)

Next f

fcnGet_Food_from_Serving_and_Pos = .Cells(fr.Row, 1).Value

End With

Set fr = Nothing

End Function

Post back if you have aany trouble transcribing that for your own purposes. I;ve uploaded the sample workbook to my SkyDrive here for your reference and download, Please note that my Excel autocorrected Entree to Entrée and I kept that in the code.

Menu_Combinations.xlsm