Find Most Recent Entry For Value Across Multiple Worksheets

Bingo969

Board Regular
Joined
Aug 2, 2006
Messages
112
Good Morning -

I'm on Win 10 and using Excel 2016. I'm wanting to find a way to find the most recent occurrence of a cell value across multiple worksheets and return the dollar amount associated with it.

I'm trying to make a summary sheet combining data from multiple sources. This will be combining invoice data. I've settled on using helper columns on each worksheet.

My source sheets will have a tab with a summary of data for the year and then a separate tab for each month containing that month's invoice. Worksheet names are Jan 19, Feb 19, Mar 19, etc. We use a new workbook each year.

Each month's invoice has a department code and then the charges for that month. I subtotal each month by department code.

What I'm wanting to do is put the helper column on that summary tab. The helper column would take a department code and look for the most current month with a subtotal and return that value.

The biggest issue I think is that since services may get changed, added, and removed all year long the cell those subtotals appears in can move up and down in each month's sheet. However, they WILL always be in 1 particular column (Column A) In other words, the values I'm looking for might be in cell A4 one month, A10 the next month, A8 the month after that etc. The charge for that month will always be in column O on the same row as the department code.

Example department code might be 51-8101.

So what I'd be looking to do is basically:
In tab SUMMARY, cell A1:
Find the most recent occurrence of 51-8101 in Column A on tabs Jan19, Feb19, Mar19 through Dec19 and return the value in in the same row from cell O

I hope this makes sense.

Thank you for any help you can offer.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,127,908
Messages
5,627,578
Members
416,255
Latest member
amethystia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top