Reference a table like in Excel
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.
Best Answer

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)))
Answers

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

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?

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?

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.

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)))

Yes distance is in a column that uses an index collect to pull from a database.
I will try this thank you very much.

=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?

Each range should only reference a single column of the reference table. I did mistype the first range name.
{Reference Table Per Diem Column}
{Reference Table Structure Column}

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

You are missing the MATCH function.

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!