Should be simple..

[Jito463]

Ok, this should be simple, but I can't seem to figure out how. I've searched here and on Google for the past hour, and I can't seem to discover how to do this.

I'm using Excel 2003 (still haven't upgraded to 2007), and I've got a spreadsheet I use for my business with 13 sheets (12 months, plus an annual tally). The primary cell of importance is always B2 on the 12 monthly sheets. It tallies up all the data from the month into a single cell (a simple =SUM formula). I want to automatically update the cells on the first sheet with the data from the corresponding monthly sheet.

For example, January is sheet 2 and the annual tally is sheet 1. I want to copy from sheet 2, cell B2 (the monthly total), to sheet 1, cell B5. For February, from sheet 3, cell B2, to sheet 1, cell B6. And so on, and so forth.

I know this should be super simple, but I can't seem to figure out the command. I would be grateful for any assistance in this. For now, I'm simply manually entering the data in the annual sheet, but I would prefer it autoupdate.

 

[xld]

Try

=INDIRECT(TEXT(DATE(1,ROW(A1),1),"mmm")&"!B2")

in B% and copy down

 

[Jito463]

While that didn't work, and turned out to be far more complicated than I was looking for, your mention of the "indirect" command got me searching online in a different path than before. Turns out, it was super simple, like I originally thought. I simply needed the following formlua in my first sheet (the annual tally)

=01!B2

Where 01 = my January totals and B2 = the cell I wanted to copy from. Simple.

Thanks for trying anyway, I really do appreciate it.

 

[xld]

Mine was generic to work out the sheet name automatically. Oh well!

 

[Jito463]

I've always preferred the Einstein approach. "Everything should be made as simple as possible, but not one bit simpler".