Unique and Indirect Functions

[jwaz73]

Hello Friends! I'm new to using what I believe are called dynamic functions in Excel. I have successfully used the INDIRECT function in this same sheet like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&tblSheets&"'!AG7:AG26"),K7,INDIRECT("'"&tblSheets&"'!AW7:AW26")))

Obviously, this will return a numeric value.

Now, trying to get a unique list of text like this:

=UNIQUE(INDIRECT("'"&tblSheets&"'!AG31:AJ40"))

This results in a #VALUE! error with the details stating "A value used in the formula is of the wrong data type."

What I'm expecting is to get multiple results that 'spill' into the cells below the formula cell.

The format of all cells involved is 'General'. (If that makes any difference.)

What I'm trying to accomplish here is to account for sheets being added to this worksheet, then the tblSheets named range can be adjusted without having to rewrite several formulas.

I'm about five hours into reading about how to use the two functions in this formula and haven't made any progress. Suggestions are appreciated.

-Many Thanks!!

 

[Fluff]

How many sheets do you have & are they all contiguous?

 

[jwaz73]

Right now there are three sheets and they are all contiguous. There will be additional sheets added in the future.

 

[Fluff]

In that case you can use a 3D reference like

=VSTACK(Sheet1:Sheet3!AG31:AJ40)

 

[jwaz73]

I started with this:

=UNIQUE(Sheet1:Sheet3!AG7:AJ26)

Doesn't work...

Ended up here:

=UNIQUE(TEXTSPLIT(TEXTJOIN(",",TRUE,Sheet1:Sheet3!AG7:AJ26),,","))

This works but the range of sheets is static and adding a sheet to the book will cause me to update formulas in several different places.

I thought about using MIN(tblSheets)!MAX(tblSheets) but reading about those functions, they only work with numbers not text.

Still looking...

 

[jwaz73]

Thanks! It looks like the VSTACK function will take the place of the TEXTSPLIT(TEXTJOIN)) complexity and that will help.

Still looking for a way to have the Sheet1:SheetN reference be dynamic...

 

[Fluff]

You can create two "dummy" sheets (which can be hidden) called Start & End then use those in the formula & make sure that any extra sheet go inbetween them.

 

[jwaz73]

Ah, hadn't thought of that. That may be the answer, at least for now...

 

[Fluff]

Glad we could help & thanks for the feedback.