Display last value in a row (range) to a cell in that row (outside of range)

Brent R

I need help with a formula to bring the last value in a row to a cell within that row. The columns between the audits have no data. I want to review the last audit week's data in a group at the end of my sheet.

Paul Newcome

My apologies. I should have waited for the coffee to kick in before responding.

=INDEX([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, 1, (COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, @cell <> "") - 3) + ((COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, @cell <> "") / 4) - 1))

Paul Newcome

Are you able to enter some mock data to show exactly what you are wanting to accomplish?

Brent R

Hope this helps...I want the lastest weekly audit information to populate in the last audit section.

Paul Newcome

Try something like this:

=INDEX([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, 1, COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, @cell <> "") - 3)

Where you see - 3 at the end, you will want to make that - 2 for the [Last Audit Date] column, - 1 for the [Last Audit Tech] column, and remove it altogether for the [Last Action/Comment] column.

Brent R

I copied the formula and made the changes you stated and I'm getting the returns below, and the ones that are returning a value is not from the last audit performed...any ideas

Paul Newcome

The columns with the errors need to be changed into date type columns.

The ones that are pulling in data... Where is it pulling from? How far off to the left or to the right is it? When I tested in my own sheet I left those "filler columns" (the black ones in between) blank. Do you have something in them?

Brent R

The 2nd and 3rd row is pulling one audit to the left, the black columns are blank. I'm sending an excel file with data, hopefully this will help...thanks

Node to PS Association Smartsheet Audit 2-5.xlsx

Paul Newcome

Sorry about that. I got a little ahead of myself.

=INDEX([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, 1, (COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, @cell <> "") - 3) + ((COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row) / 4) - 1))

Try the above. Again, you will need to adjust that -3 to -2 to -1 and then get rid of it as you move from left to right.

Brent R

When I paste in the above I get "#INCORRECT ARGUMENT SET"

Paul Newcome

My apologies. I should have waited for the coffee to kick in before responding.

=INDEX([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, 1, (COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, @cell <> "") - 3) + ((COUNTIFS([1st Audit Week Ending Date]@row:[10th Action/Comments]@row, @cell <> "") / 4) - 1))

Brent R

Thank you so much, it works...yay

Paul Newcome

Happy to help. 👍️