Help converting excel formula to smartsheet

chuck.peoples

Hello! I'm working on converting a labor budgeting spreadsheet from Excel to Smartsheets. I've been able to convert most of the formulas over but I'm stumped on this one. I'm trying to reference a few other sheets to collect name and zone they're working in.

I have this formula for name:

=IF('Outbound Staffing Requirements'!$B$1="Monday",'Monday Staffing'!A2,IF('Outbound Staffing Requirements'!$B$1="Tuesday",'Tuesday Staffing'!A2,IF('Outbound Staffing Requirements'!$B$1="Wednesday",'Wednesday Staffing'!A2,IF('Outbound Staffing Requirements'!$B$1="Thursday",'Thursday Staffing'!A2,IF('Outbound Staffing Requirements'!$B$1="Friday",'Friday Staffing'!A2,'Saturday Staffing'!A2)))))

And this one for task:

=IF('Outbound Staffing Requirements'!$B$1="Monday",'Monday Staffing'!C2,IF('Outbound Staffing Requirements'!$B$1="Tuesday",'Tuesday Staffing'!C2,IF('Outbound Staffing Requirements'!$B$1="Wednesday",'Wednesday Staffing'!C2,IF('Outbound Staffing Requirements'!$B$1="Thursday",'Thursday Staffing'!C2,IF('Outbound Staffing Requirements'!$B$1="Friday",'Friday Staffing'!C2,'Saturday Staffing'!C2)))))

Ideally, I'd be able to select the cell and drag down the smartsheet to apply the formula to all names and zones below.

Thank you in advance.

Paul Newcome

Are you able to provide screenshots of your existing Smartsheet structure?

chuck.peoples

Thank you!

Amber Eakin

You'll need to create a cross sheet reference. Excel uses the name and those markers like !, but Smartsheet cross sheet reference works a bit differently.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Paul Newcome

Are you able to manually enter some of the desired outcome to reflect what you are trying to automate through formulas? I'm still not sure I follow exactly what you are trying to accomplish.

chuck.peoples

I have 6 sheets “‘Monday Staffing”, “Tuesday Stafffing” etc. I’m trying to get the top sheet to reference a cell that has the day of the week and then references the Staffing sheet accordingly to parse the values in the first spreadsheet I snapped a screenshot of.

Paul Newcome

How exactly are you planning to establish which sheet to pull from?

What exactly is supposed to be populated in the first screenshot? What would that look like if there were no errors?