TheFeniX

So, I've got some logs for a monthly deal. This whole spreadsheet consists of separate sheets (one for each day of the month) and a summary table.

Now, for one of the summary cells, I have this formula: ='1'!G17

This makes a call to one cell from the "Day 1" spreadsheet. Now, I've got to do this SAME cell for sheets 2-31 for the summary. If I copy and paste the formula down the list, excel trying to be smart, makes it go:

='1'!G17
='1'!G18
='1'!G19

and so on. That's obviously not what I need. I need excel to go down the list like this:

='1'!G17
='2'!G17
='3'!G17
='4'!G17

and so on.

Any ideas? I can go at my current rate, but I'm looking at many more hours of work as there's about 10 calls per sheet and 31 days. It adds up.

__________________

JAMerica

try ='1'!$G$17

The $ should fix the column/row number in formulas

__________________

--------------------------------------------
Jamerius (Feral Druid)

MagnaCentipede

I don't think the methods of autocomplete will work the way you want it to, here. You could place a string to lock the row value (!G$17), but that won't help the sheet page counter.

You might check for arcane functions or methods; there might be a built-in way to sum the same cell through a range of pages. Excel needs to do something useful to justify it's weight, right?

Sorry I'm not of more help; I don't use multipage spreadsheets with any frequency.

__________________

TheFeniX

I found a cop-out: by doing one row manually, then copying and pasting, it keeps the '1', '2', '3', etc numbering and only messes up the letter of the second variable. Ex: '4'!G17 becomes '4'P17. But the number stays the same.

It works and is faster. Oh well.

__________________

TheFeniX

Ok, another round-about. I pasted in the formulas, then did a find/replace to put in the numbers I needed.

Good Times......

__________________

Catman1975

Like JAMerica said, to keep the column and rows from changing, put a $ before the one that you don't want to change Ex: '4'!$G17, Then copy and paste and the columns will not change.

__________________

Dick Blonov

This formula will copy properly:

=INDIRECT("'"&A1&"'"&"!A6")

Just paste that in the cell where you want your summary.

Set A1 to A31 on the summary sheet to = 1 to 31 (the name of the daily sheets).

A6 is the position on the daily sheet that you want to add up (like daily total I guess). So if you are summarizing multiple daily totals, just replace that A6 with whatever cell contains that total and leave the rest intact.

If you want to call your sheets «Day 1» to «Day 31», change the formula to:

=INDIRECT("'"&"Day "&A1&"'"&"!A6") or fill A1 to A31 with whatever sheet names you have (Day 1, Day 2, etc).

Is that what you were looking for ?

( note the highlighted double quotations marks in red in the first example, single quote marks are bold)

DB

__________________
|TG-6th|Blonov