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!!

 

[ExceLoki]

try this

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

 

[jwaz73]

try this

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

Thank you for the suggestion! Unfortunately, it's the same result.

 

[ExceLoki]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

 

[Fluff]

I don't think you can use unique & indirect like. Do you have the VSTACK function yet?

 

[ExceLoki]

try this

=TOCOL(INDIRECT("'"&"tblSheets"&"'!"&"AG31:Aj40"))

 

[Fluff]

@ExceLoki I suspect that tblSheets is a named range not a sheet name.

 

[jwaz73]

Thank you, both! Let me explore the VSTACK function and if needed I'll post some sample data.

 

[Fluff]

Ok

 

[jwaz73]

So, yes. I can use the VSTACK function in a formula. Looking at the docs for VSTACK I see it accepts only an array of cell ranges. Haven't tried anything yet.

Is there a better way to handle a dynamic range of worksheets other than the INDIRECT function? @Fluff was correct, I am using the named range 'tblSheets' as a list of the worksheets to include in the formula.