Reference a table like in Excel

Bwoods113

I am working on a per diem sheet that will automate the calculation of distance between the employees address and the jobsite. I have an excel sheet that our payroll has been using that I am trying to replicate some of the formulas to keep things the same. I have run into a formula that references a group of cells for a calculation (see attached). I am wondering the best way to do this in smartsheet. I have attached an image of how my sheet is structured. I am trying to put the formula into the per diem column. Appreciate any insight into this. Thank you for your time.

The green group of cells is what I am having trouble replicating in a formula.

Paul Newcome

Ok. And do you have a Distance column? I would put the table on a separate sheet and then use this formula:

=IF(Distance@row< 30, 0, INDEX({Reference Table Per Diem Sheet}, MATCH(ROUNDUP(Travel@row / 30 + 2), {Reference Table Structure Column}, 0)))

Paul Newcome

Do you mean you are having trouble creating the table or referencing the table?

Bwoods113

@Paul Newcome

I appreciate your reply still struggling with this, this is what I have in my formula and table now. Wondering what I have wrong in my formula, and also wondering if I could put this table in a different sheet and reference it that way?

Paul Newcome

So your question is about referencing the table then? Are you able to clarify where each field is in the Smartsheet in comparison to the Excel? For example, part of your formula is D13 divided by C4. Which column in Smartsheet houses D13's data and which column in Smartsheet houses C4's data?

Bwoods113

My apologies, D13 is the same as Travel@row, C4 is reference the cell that is $30 so I have just put that into my formula as 30.

Paul Newcome

Ok. And do you have a Distance column? I would put the table on a separate sheet and then use this formula:

=IF(Distance@row< 30, 0, INDEX({Reference Table Per Diem Sheet}, MATCH(ROUNDUP(Travel@row / 30 + 2), {Reference Table Structure Column}, 0)))

Bwoods113

Yes distance is in a column that uses an index collect to pull from a database.

I will try this thank you very much.

@Paul Newcome

Bwoods113

=IF(Distance@row < 30, 0, INDEX({Per Diem Table Range 1}, MATCH(ROUNDUP(Travel@row / 30 + 2), {Per Diem Table Range 2}, 0)))

Getting an invalid value, I just want to make sure I have the data correct {Per Diem Table Range 1} references the sheet that I put the table into, and {Per Diem Table Range 2} references the cells of the table?

Paul Newcome

Each range should only reference a single column of the reference table. I did mis-type the first range name.

{Reference Table Per Diem Column}

{Reference Table Structure Column}

Bwoods113

That worked perfect thank you so much, On the next one I just have an incorrect argument any idea why?

=ROUNDUP(INDEX({Per Diem Table Range 3}, ROUNDUP(Travel@row / 30 + 2, 0), {Per Diem Table Range 5}, 0))

This one I am referencing that same table but the % column

Paul Newcome

You are missing the MATCH function.

Bwoods113

@Paul Newcome Appreciate all your help and willingness to share your knowledge.

Paul Newcome

Happy to help. 👍️