Copy and paste?

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello all,

I have a link inside of a numerous amount of worksheets in one
workbook. I am trying to copy and past a cell link but need it to add on
number without having to do it manually. It brings all the totals from
each page (specific cell) to one (totals) page.

The link is =SUM('4981'!$G$46)
4981 is the sheet and $G$46 is the cell referenced
I need to copy and paste this to the next cell below with the numbers as
follows =SUM('4982'!$G$46) I have asked quite a few college grads and
other Excel "masters" I know and yeeks.... no one knows how. Is it
possible?????

If anyone can help I would appreciate it very much.

Thank you........................ SKEETER
 
Jim,

first of all, there seem to be no need to use sum in this case since it is
only one cell
This formula will increase the sheet number if copied down

=INDIRECT("'"&ROW(4981:4981)&"'!G46")

the only drawback is that if you insert a row above the formula it will
increase the sheet number
That could be avoided with this formula

=INDIRECT("'"&ROW(4981:4981)-ROW($A$1)+1&"'!G46")
 
Put the list of your sheet names (4981, 4982 and so on, as numbers) in
an empty Col., say from A1 down. Now enter in B1
=INDIRECT("'"&TEXT(A1,0)&"'!"&$G$46)and copy down.
Ilan
 
I belive the text function is superfluous, but I don't think your formula
would work
regardless. Maybe if you change it a bit like this

=INDIRECT("'"&A1&"'!G46")
 
Really!

I copied and pasted your formula into excel and I get a #REF! error
with 8291 in A1 and a sheet named 8291 in the same workbook. Excel 2000 and
2003
 
Sorry, I apologize. I did not mention that in c1 I entered G46, and,
of course, the formula should be =INDIRECT("'"&TEXT(A1,0)&"'!"&$C$1).
I used the Text function to convert numbers to texr, as it is easier
to enter a column of numbers.

Ilan Rencus
 
Thank you all for your propmt replies. Peo gave the answer that worked
for my application (Thank You Peo........)

Thanks again..................... Jim
 
Back
Top