Forum Discussion

shade206's avatar
shade206
Brass Contributor
Dec 24, 2019
Solved

VLOOKUP and INDIRECT Formula assistance needed

I have TWO issues.

#1 For some reason My VLOOKUP and INDIRECT combination formula for the chart is not retrieving the data and rather just the IFERROR. I converted all the numbers correctly, at least I believe I did.

 

#2 I have a dropdown selection titled "ALL" which I want to retrieve the SUM'd up information across all three tabs when selected. Is this possible? 

 

What in SAM HILL TANRANTION is going on?!?!

  • shade206 

    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

     

11 Replies

    • shade206's avatar
      shade206
      Brass Contributor

      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 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        shade206 

        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

         

Resources