• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic reference to static table names

C

CLSmith

New Member
I need to use a dynamic reference to table names in a formula.

I have a worksheet with multiple tables each containing multiple columns and rows. The tables have the defined names ‘plan1’ and ‘plan2’ sequentially.

I have another named table, lstPlans, with top-level information about each of the detailed plans (i.e., tables plan1, . . . plani). In lstPlans, I need a column with a formula that is the SUMPRODUCT of 2 columns in a corresponding plan. So for the first plan in lstPlan, the formula is =SUMPRODUCT(plan1[ColX],plan1[ColY]).

I’d like to write a relative formula so I can take advantage of table properties for lstPlans. That means then that I can’t directly refer to “plan1” since the 2nd entry in lstPlans needs to refer to table “plan2”, the 3rd to “plan3,” etc., so I need to use a formula to concatenate the text “plan” with a number that corresponds to the position number of the summary data in lstPlans. I can do that using “Concatenate” function and “Row” functions in lstPlans. But, I haven’t yet been able to use that result in a relative reference to the table name successfully.

How would I structure something, for example, like =SUMPRODUCT(“relativereference to table here”[ColX],”relativereference to table here”[ColY]). Any suggestions for how to structure that reference??
 
First, create a named formula which is a list of all the table names. Let's call it AllPlans
=plan1,plan2,plan3,plan4,plan5

Now, we can use the INDEX function. The rarely used 4th argument of INDEX lets you specify which area you want. Let's say that in our formula table, we want to do a sumproduct of 4th column with 5th column, and every row we want to look at a different table. Formula then is:
=SUMPRODUCT(INDEX(AllPlans,,4,ROWS(A$1:A1)),INDEX(AllPlans,,5,ROWS(A$1:A1)))
As we copy this down, the ROWS function will increment in value, and we'll pull data from the 2nd, 3rd, etc. area within AllPlans, which corresponds to our tables.
 
PS. It is possible to build the table name and then use the INDIRECT function, but IMO, this is not as good a solution since INDIRECT is a volatile function, and with SUMPRODUCT involved, it's probably best to avoid needless calculating. :)
 
How many SUMPRODUCTS do you think you will end up with in your workbook, and how large are the ranges that you're going to be pointing them over.

If the answer is 'many' and 'long', then you're probably going to want to rethink your approach. If possible, use PivotTables to accomplish what the SUMPRODUCTS are doing.

Sounds like the CHOOSE function could be helpful for what you're proposing also.

Even better, upload a sample file, so we can take a look at the bigger picture.
 
So I've uploaded a simple file that I've started working with.
On the Details tab, cell E2 has the calculation I'd like displayed in cell G5 on the Summary tab.
Since cell G5 is part of a table, I'll need a common formula to use for each row. So cell G6 would display the value in M2 on the Details tab.
My thought is that since the details tables are named sequentially (plan1, plan2, etc.), then I should be able to use a formula to refer to the tables, but Excel doesn't seem happy with a dynamic name for the tables (or, rather, I've not been able to satisy its demands!).
If you notice also, in the summary table, I'll need formulas to calculate the total planned budget and total executed funds from each project table as well.
Maybe I'm overcomplicating things and shouldn't worry about standard structure using a table on the Summary tab. I don't expect more than a few dozen projects, but I hate to have to manually add links for each row in the table on the Summary tab.
Thanks for all suggestions!
 

Attachments

  • Summary.xlsx
    21.3 KB · Views: 9
Hi ,

Try this :

=OFFSET(INDIRECT((tblProjects[[#This Row],[Row ID]]) & "[#Headers]"),-2,3,1,1)

In case you have Excel 2010 , you can use a slightly shorter :

=OFFSET(INDIRECT((tblProjects[@[Row ID]]) & "[#Headers]"),-2,3,1,1)

Narayan
 
Thanks for everyone's suggestions. I wasn't able to use a variable reference to the specific details, so I just punted and added the summary values to my details sheet, then used a standard OFFSET reference on the summary page to pull the desired values to my summary sheet. Maybe when I have more time, I'll go back and keep trying to get the ampersand / concatenation usage correct . . .
 
You must log in or register to reply here.
Back
Top