Multi sheet log with summary sheet that auto updates

I am trying to keep tract of an account. The account value could go up or down each month so I don't think MIN or MAX will work for me. I have a sheet for each month and a summary sheet that I want to report the latest month's value I have entered.

For example: If sheet (JAN) has a value of $200 in cell A2 and sheet (FEB) has a value of $190 in cell A2 then I want my (Summary) sheet to have a value of $190 in cell A2. But once I enter a new value, say $210 into cell A2 of the (MAR) sheet then I want cell A2 of the (Summary) sheet to change automatically and report $210. I really don't want to do a bunch of IF statements if I can help it. I searched for an answer and found similar formula's that use INDIRECT but I'm not sure how I would use it for what I am trying to do. I have attached a log to this that kind of represents what I want. It doesn't have any code but the values and results are what I'm looking for. Any help would be greatly appreciated. Thanks!

multi sheet test last entry.xls17 KB
multi-sheet-test-last-entry-Nick.xls24.5 KB

Its just a max formula.

Its just a max formula.

Nick's picture


there's no way to do this without VBA

- the issue you have is that sheets are being added and an Excel formula cannot deal with this occurrance.

If you add all the monthly sheets, you can do a large IF formula, but that's ugly.

I suggest you have one sheet, and store the data in rows.

Here's my solution:




Thanks for the information Nick. I will give it a try.