Pulling info from another sheet using pre-defined row and column labels/inputs
Looking for a formula and stuck. Not sure if vlookup, match, index, or a crazy combination of each.
I have a metrics sheet where I want to pull in the number of hours from a another sheet. The source sheet I need to pull from has hours for each month listed (2nd screenshot). However, the source sheet has 3 sets of hours for each month based (different columns, standard, OT, etc).
Now, my metrics sheet has "labels" for the month and the columns that will be used in the formula (1st screenshot). I want this in order to manually change the labels at will and still search the source sheet to pickup the proper number of hours.
How in the world to I write a formula for this?
Answers
-
=IF(
(CONTAINS([Label]@row,(REFERENCE RANGE 1))=True),
(VLOOKUP([Label]@row,(REFERENCE RANGE 2),5,false)),
"")
Where reference range 1= [Month]:[Month]
, reference range 2= [Month]1:[Standard]X
Do click, reference other smartsheet, the second smartsheet and select the range accordingly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!