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!
| Attachment | Size |
|---|---|
| multi sheet test last entry.xls | 17 KB |
| multi-sheet-test-last-entry-Nick.xls | 24.5 KB |
Its just a max formula.
Its just a max formula.
summary
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:
multi-sheet-test-last-entry-Nick.xls
Nick
Thanks!
Thanks for the information Nick. I will give it a try.