Dynamic Sheet reference based on a cell value

Anand B

Hi,

I am new to Smartsheet and I have three sheets,

Sheet 1 - Contains employee regular hours pay rate
Sheet 2 - Contains employee overtime hours pay rate

Sheet 3 - Calculates the overall pay of a employee per month including regular and overtime hours.

I need help with a formula in Sheet 3, where the "Pay" column should auto-populate with "Pay" details by referring Sheet1 and Sheet2. When I choose "Regular hours" in the work date column(as highlighted above), it should pull Regular Pay/Hr from Sheet 1. When I choose "Extended Hours" in the Work Date column, it should pull Extended Pay/H from Sheet 2. Please let me know if this can be achieved.

Note: I don't want to club both "Regular" and "Extended" hours Pay details into a single sheet. I want to have them in separate sheets for some business reasons. The above information/sheets are just a simplified version of actual data to get expert's help.

RDRGSJ00

Hello Anand B,

I came up with the below solution, I hope it helps:

First, you need to create two helper columns (you can hide these columns after):

[Helper Regular]: This column will bring in the regular pay with the below formula:

=IFERROR(INDEX(COLLECT({Regular}, {Name}, Name@row), 1), "--")

{Regular} = the regular pay from Sheet 1
{Name} = the name from Sheet 1
Name@row = the name on Sheet 3

[Helper Extended] = This column will bring in the extended pay with the below formula:

=IFERROR(INDEX(COLLECT({Extended}, {NameEx}, Name@row), 1), "-")

{Extended} = the extended pay from Sheet 2
{NameEx} = the name from Sheet 2
Name@row = the name on Sheet 3

Then in the [Pay] column, I converted it to a dropdown with the values "Regular Pay" and "Extended Pay" and I have the below formula:

=IF([Work Date]@row = "Regular Hours", [Helper Regular]@row, IF([Work Date]@row = "Extended Hours", [Helper Extended]@row))

This will show regular pay hours or extended pay hours depending on which option you select.

RDRGSJ00

Hello Anand B,

I came up with the below solution, I hope it helps:

First, you need to create two helper columns (you can hide these columns after):

[Helper Regular]: This column will bring in the regular pay with the below formula:

=IFERROR(INDEX(COLLECT({Regular}, {Name}, Name@row), 1), "--")

{Regular} = the regular pay from Sheet 1
{Name} = the name from Sheet 1
Name@row = the name on Sheet 3

[Helper Extended] = This column will bring in the extended pay with the below formula:

=IFERROR(INDEX(COLLECT({Extended}, {NameEx}, Name@row), 1), "-")

{Extended} = the extended pay from Sheet 2
{NameEx} = the name from Sheet 2
Name@row = the name on Sheet 3

Then in the [Pay] column, I converted it to a dropdown with the values "Regular Pay" and "Extended Pay" and I have the below formula:

=IF([Work Date]@row = "Regular Hours", [Helper Regular]@row, IF([Work Date]@row = "Extended Hours", [Helper Extended]@row))

This will show regular pay hours or extended pay hours depending on which option you select.

Anand B

@RDRGSJ00 Thanks for the guidance and I was able to pull the needed data.