Using Values in a Named Range as Sheet References in a Formula

Copper Contributor

Hey all - Bit of an odd one here, and the answer may require some VB coding, but thought I would start here.

I am building a spreadsheet to keep track of data for members of a club. Each member has their own worksheet form where we track data on a week-by-week basis. Weekly, monthly, quarterly, and annual summaries are also reported on. 

I have all of the formulas in place, and it works as I want to for now, but the sticking point is this: We face a constantly changing group, where new people come in, current people leave, etc.

 

I have created a dynamic named range on one sheet to keep track of the members, and that solves some of my calculation issues. What I would like to do is use the values in the named range as sheet references in formulas, because we calculate things like who did the best at X, Y, and Z each week / month / quarter / year.

Here's an example:  For formula purposes, my named range looks like this:

BOB

JIM

SAM

 

Each of these people has their own worksheets, which I have also named BOB, JIM, and SAM.

When I do a calculation, I can use the BOB:SAM range to tell Excel to reference the same cells across different sheets.

 

So, now Pete decides to join the group, so my dynamic named range now looks like this:

 

BOB

JIM

SAM

PETE

 

When Pete joins the group, I also have to add PETE as a worksheet, which means I also have to manually modify many of my formulas to now use the range BOB:PETE.

 

So... Is there a way for me to take a formula like this:

 

=IF($A$3>=B31,MIN('BOB:PETE'!J38),"")

 

and replace the 'BOB:PETE" with a named range, so Excel knows to check across worksheets named like the entries in my dynamic named range?

 

Thanks!

5 Replies

@ArchieGoodwin 

The idea is here https://exceljet.net/formula/3d-sumif-for-multiple-worksheets

For such data

image.png

formula in C4 (to take average) is

=SUMPRODUCT(
  AVERAGE(
    INDIRECT("'" &
       $B$4:INDEX($B$4:$B$50,COUNTA($B$4:$B$50)) &
       "'!" &
       ADDRESS(ROW(A1),COLUMN(A1))
    )
  )
)

You have list of your sheets, use dynamic range formula to return it, substitute to INDIRECT. Not to hardcode cell reference like "A1" we use ADDRESS. Combine and wrap by INDIRECT. Apply our function, AVERAGE, to it. Since formula doesn't work with 3D array directly, we wrap all together by SUMPRODUCT.

@ArchieGoodwin 

This just picks up on one aspect of your problem, namely adding sheets to a 3D range.  The 'normal' process is to 'top and tail' your member sheets with further blank sheets MEMBERS and END.  Provided the formulas can cope with blank cells, they may be applied to the 3D range MEMBERS:END.

 

Another function that can be useful is CHOOSE which will 'glue' together named ranges (here columns N)

= CHOOSE({1,2}, Sheet1!N, Sheet2!N )

to form a 2D array that may then be used in formulas such as

= MMULT( array, {3;1} )

@Peter Bartholomew 

Peter, from my point of view Start:End approach is not reliable if sheets are added/removed quite often. Probability that next BOB appears after END is quite high. Plus addition efforts to work with formulas to exclude zeros (or pre-defined values) in Start and End.

@Sergei Baklan 

Hi Sergei

I wasn't claiming it was robust!  It was just a way of ensuring that the sheets could be inserted without needing to play 'hunt the reference'.  I agree that robust is better but it almost runs counter to the 'Wild West' spirit in which almost all spreadsheets are written.

Peter

Thanks to both of you, @Peter Bartholomew @Sergei Baklan - Something for me to work with now. I'll try both methods, and come back with my comments on each. Much appreciated!