Pulling info from another sheet using pre-defined row and column labels/inputs

Options

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

  • Intern98
    Intern98 ✭✭✭
    Options

    =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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!