Nested Sumif/Indirect/Vlookup

[gmittar]

Hi guys,

I have a sumif formula that I'm trying to link to dynamic tab names based on a drop down. I'm fairly new to indirects, but got the function to work with explicit sheet names. However, when I throw in a vlookup to build a dynamic sheet name it breaks. Any help is appreciated.

Here's the formula that isn't working, it returns #Ref ! The Vlookup is looking for the first part of the sheet name, and the second part is constant. I've removed the latter part of the formula as it hasn't changed from the working version.

=SUMIFS(INDIRECT("'"&VLOOKUP($B$1,$S$3:$T$13,2,FALSE)&" Second Part of Sheet Name'"&"!"&"$C$98&:$&NC$98")

<colgroup><col></colgroup><tbody>
</tbody>

 

[dave2018]

hi,

i don't have excel available now, but first of all, these ampersands look extra in your formula

=SUMIFS(INDIRECT("'"&VLOOKUP($B$1,$S$3:$T$13,2,FALSE)&" Second Part of Sheet Name'"&"!"&"$C$98&:$&NC$98")

 

[RasGhul]

https://www.youtube.com/watch?v=GUClkvJ7Gag

 

[Aladin Akyurek]

Care to post the formula which you want to transform to one that uses INDIRECT?

 

[gmittar]

Thanks guys, I got it working. My issue was that I had way too many quotation marks in my formula. When I removed all but the ones at the beginning of the named portion and the end of the range, it worked.