Display last value in a row (range) to a cell in that row (outside of range)
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.
Best Answer
-
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))
Answers
-
Are you able to enter some mock data to show exactly what you are wanting to accomplish?
-
Hope this helps...I want the lastest weekly audit information to populate in the last audit section.
-
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.
-
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
-
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?
-
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
-
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.
-
When I paste in the above I get "#INCORRECT ARGUMENT SET"
-
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))
-
Thank you so much, it works...yay
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!