Sheet Summary - same sheet VLOOKUP with TODAY()

Matthew Best
Matthew Best ✭✭
edited 07/05/23 in Formulas and Functions

Hi,

My Column Headers are:

Date, Total Opened Cases, Total Closed Cases, Total Backlog.

I am attempting, and failing, to use the following variations of a VLOOKUP formula. Can someone help??

=VLOOKUP(TODAY(), {Date}:{Total Backlog}, 4, false) = #UNPARSEABLE

=VLOOKUP(TODAY(), {Date:Total Backlog}, 4, false) = #INVALID REF

=VLOOKUP(TODAY(), Date:Total Backlog, 4, false) = #UNPARSEABLE

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Then you don't need to use the cross sheet reference (in the { } brackets), you would just select the columns. If they are sequential (like in the example) then your formula would be:

    =VLOOKUP(TODAY(), Date:[Total Backlog], 4, false)

    If they are more spaced out then this would need altering, but from your example I am guessing they are grouped together.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Matthew Best,

    The middle one looks to be correct formula wise, but have you set the reference correctly when putting the formula in? If you're trying to create the cross sheet reference without doing this it won't work.

    If you're not sure how, this should show you:

    Click on this when entering the formula. On the screen which opens, find your sheet to reference in the list on the left (you can search by its name to find it) and highlight the columns required and rename the Sheet reference name (as it will default to the sheet name plus range and a number):

    Hit the OK button in the bottom right of this screen and finish the VLOOKUP and everything should work:

    Hope this helps, but if it's something else then please let us know so we can try and get it fixed.

  • Thank you for your answer @Nick Korna.

    However, the 'Reference Another Sheet' will not allow me to select the sheet I am currently in. It only allows me to select an different sheet?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Then you don't need to use the cross sheet reference (in the { } brackets), you would just select the columns. If they are sequential (like in the example) then your formula would be:

    =VLOOKUP(TODAY(), Date:[Total Backlog], 4, false)

    If they are more spaced out then this would need altering, but from your example I am guessing they are grouped together.

  • Thanks this worked. But I don't understand why. Why doesn't Date have [] but Total Backlog does have them?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    The square brackets are for if your column header has a space in. So "Date" wouldn't need them, but "Total Backlog" does. If "Date" was "Start Date" or "End Date", then this would also need the [ ] around it for the reference.

  • Thanks much appreciated. 👍️

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to have helped out! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!