Sheet Summary - same sheet VLOOKUP with TODAY()
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
-
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
-
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?
-
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?
-
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. 👍️
-
No problem, happy to have helped out! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!