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