Need help writing Macro to help simplify a function

[mbrittb00]

I've got an entry in one (actually several) of my cells on a spreadsheet that is a bit unwieldy. It is actually made up of several similar strings of nested functions that are just begging to be put into a macro, but I'm not sure where to start. Here is the function I am trying to simplify.

={SUM((INDIRECT(A4&INDEX(SheetRanges,MATCH(A4,Worksheet_Names,0),MATCH("Unit",RangeHeadings,0)))=B4)*(INDIRECT(A4&INDEX(SheetRanges,MATCH(A4,Worksheet_Names,0),MATCH("Type",RangeHeadings,0)))=C4)*(INDIRECT(A4&INDEX(SheetRanges,MATCH(A4,Worksheet_Names,0),MATCH("Inc",RangeHeadings,0))) = "x"))}

Note that "SheetRanges" "Worksheet_Names" and "RangeHeadings" are all defined ranges via the "Name Manager". As you can see this is made up of three nested lookups that are identical except for the second match target. I was hoping to be able to take this portion:

INDIRECT(A4&INDEX(SheetRanges,MATCH(A4,Worksheet_Names,0),MATCH("Unit",RangeHeadings,0)))

And convert it into a macro function that takes A4 and "Unit" as inputs and returns a Range.

The problem is I'm not sure where to start. Any help would be appreciated.

 

[mbrittb00]

I had gotten an e-mail telling me that I had a response to this, but there is no response. Strange.

 

[mikerickson]

I posted an inaccurate response and deleted it. That both put it in my Subscribed list and (apparently) triggered an e-mail to you.

As a general suggestion, I would suggest that you extend the use of Names to named functions. For example, your formulas would look cleaner if you defined
Name: nameMatch
RefersTo: =MATCH(A4,Worksheet_Names,0)

Similarly, names like unitMatch, typeMatch and incMatch could help clean up the formula.

In general, it looks like you have a chart of ranges for particular combinations. Without knowing a lot more about your spreacheat, my first guess is that, in combination with the INDIRECT function, is a clunky way to approach your overall goal.

BUT, if you understand it, its better than a cleaner approach that you don't.

 

[mbrittb00]

I was able to get an macro function to work, but as with any macro functions that are used in a lot of placed in a spreadsheet it is slow to calculate. So I'm still interested if there is way to do it with intrinsic functions.

I do like the idea of named functions, that is something I've not ever tried before. However, A4 is a variable that changed based on while line you are calculating data for (i.e. it may be A4, A5, A6, etc). Can you pass variables to named functions?

Ok, now on to what I'm trying to do. I've designed this particular workbook to help keep track of the kids school grades. I've got one sheet for each subject (i.e. History, Math, etc), that contains a list of assignments, due dates, and grade. Also included on these sheets are columns for various other information (i.e. is the assignment Homework, a Quiz, Test, etc). I then have a another sheet I am using to accumulate a summary for each of the subject, including chapter by chapter grades as well as overall grades. There are also columns on the summary sheet that breaks the grades down by 9 week grading periods. This is there the formulas comes in. Previously the formula I had posted looked like this :

={SUM((Math!UnitCol=B4)*(Math!TypeCol=C4)*(Math!IncCol="x"))}

For each Chapter/Unit of each subject there are ~30 similar formulas making various calculations.

This is very simple and concise, and has worked great over the last couple of years. However there is one drawback. The named regions (i.e. Math!UnitCol) are defined (using an OFFSET command) on each individual subject sheet. When I go to add a new subject, I may a copy of an existing subject sheet, then on the summary sheet, I have to copy a summary section of an existing subject, then adjust those 30 forumlas to point to the new subject sheet I added, then copy them down through each of the chapters/units. Very tedious.

This lead me to try and find a while to reference the "sheet" portion of those named ranges as variable (i.e. A4) on the summary sheet. What I ended up doing was creating a "Worksheets" sheet that contained a list of the different worksheets. Then in the following columns on that sheet, I used a complicated ADDRESS function (with MATCH, INDIRECT, and OFFSET function) to derive the ranges of data (i.e. "Unit", "Type, "Inc", etc), in string format. That then let me to the formula I originally posted with the use of the cross reference into that table and the INDIRECT calls.

Note that in the forumlas I posted A4 points to a cell containing "'Math'!", B4 points to a cell with the Unit/Subject Name, and C4 points to a cell with the assignment type. In this case "Homework".

I'm sure that there is a better way to do what I'm trying to do, and to be honest I'd love to learn how to do it right. Any help you could offer would be greatly appreciated.

Thanks,

Britt

 

[mikerickson]

However, A4 is a variable that changed based on while line you are calculating data for (i.e. it may be A4, A5, A6, etc). Can you pass variables to named functions?

You can't pass variables to named functions, but you can use relative/absolute addressing.
If you select B1 and define a function
Name: myFunction
RefersTo: = Sheet1!$A1 + 1
putting =myFunction will return 1 more than the cell in column A of the cell holding the function.

 

[mbrittb00]

Unfortunately I don't think that is going to help with what I'm trying to do.

Any ideas on how to resolve my situation with intrinsic functions?

 

[shg]

Does this work?

=SUM(INDIRECT(A4 & INDEX(SheetRanges, MATCH(A4, Worksheet_Names, 0), MATCH({"Unit","Type","Inc"}, RangeHeadings, 0))) = CHOOSE({1,2,3}, B4, C4, "x"))

Overall, it sounds like your data analysis would be better suited to a single all-inclusive database on one sheet and a pivot table.

 

[mbrittb00]

I tried that forumla and it didn't give me the correct result. Unfortunately I haven't had time to figure out why. As for using a pivot table, that is the one aspect of Excel that I have no experience with.