Forum Discussion
VLOOKUP and INDIRECT Formula assistance needed
First, name list of the sheets as sheets (or whatever name your prefer)
Second, correct column number in your latest version of VLOOKUP(), it shall be on one more in each formula.
Third, make branches for the selection from drop-down list, one for ALL and another one for any other name
IF($D$6="All",<sum on all sheets>, VLOOKUP as before)Next, adjust for your ranges formula from the link and combine all together. In F10 it'll be
=IFERROR( IF($D$6="All", SUMPRODUCT( SUMIF( INDIRECT("'"&sheets&"'!"&"$B$2:$B$10"), $E10, INDIRECT("'"&sheets&"'!"&"$C$2:$C$10") ) ), VLOOKUP( $E10, INDIRECT("'"&$D$6&"'!$B$1:$f$11"),2,FALSE ) ), "Day Not Worked" )similar for next columns and drag first row to the bottom.
Additionally, I'd suggest to take day of the week from the date to avoid extra errors
1) Use lookup column as left most, other words
=IFERROR(VLOOKUP($E10,INDIRECT("'"&$D$6&"'!$B$1:$f$11"),2,FALSE),"Day Not Worked")2) It's possible but the question is do you have always these 3 sheets or they could be changed. The idea for ALL is here https://exceljet.net/formula/3d-sumif-for-multiple-worksheets. Not exactly your case, just direction.
Thank you for correcting my silly mistake on the VLOOKUP, i knew that! Haha...
The REAL question than is how do i associate that Ability for the "ALL" in my drop down list, i saw your link and understand the formula but could you walk me through it a little more? Think, "Excel Formulas for Dummies" 🙂
I'm not sure how i'd keep it in that same drop down list... I'm providing the updated sample worksheet.
SergeiBaklan
First, name list of the sheets as sheets (or whatever name your prefer)
Second, correct column number in your latest version of VLOOKUP(), it shall be on one more in each formula.
Third, make branches for the selection from drop-down list, one for ALL and another one for any other name
IF($D$6="All",<sum on all sheets>, VLOOKUP as before)Next, adjust for your ranges formula from the link and combine all together. In F10 it'll be
=IFERROR( IF($D$6="All", SUMPRODUCT( SUMIF( INDIRECT("'"&sheets&"'!"&"$B$2:$B$10"), $E10, INDIRECT("'"&sheets&"'!"&"$C$2:$C$10") ) ), VLOOKUP( $E10, INDIRECT("'"&$D$6&"'!$B$1:$f$11"),2,FALSE ) ), "Day Not Worked" )similar for next columns and drag first row to the bottom.
Additionally, I'd suggest to take day of the week from the date to avoid extra errors
I walked through all your steps from scratch and did them all on my own to ensure I understood. However, i'm stuck on one thing, i cannot get my versions "ALL" to work. ... a little help? I've attached it. SergeiBaklan
Since you renamed sheets on List you shall update formulas accordingly. Other words all parts where sheets appears like
...INDIRECT("'"&sheets&"'!"&"$B$2:$B$10")...shall be changed on
INDIRECT("'"&List&"'!"&"$B$2:$B$10")