fomulas; copy formulas but only changing sheet

[Don Franco]

Hi,

We all know, I guess, what Excel does when copying a formula down.
Cell A1 will become cell A2, etc.

However. I have a 'index' sheet with the most basic info like name,
adress and identification number.
This info comes from the sheets that can be found in the document.
For example:

The index sheet looks like this:
A Sheet 1
B Don Franco
C Sunflower 34
D Netherlands
E ID 3255

On the second sheet, which is called '1', you will find these exact
information because the index has the formula which refers to cells in
sheet 1 and column B is hyperlinked to sheet 1, 2, 3, etc. So; by
clicking the name of de person, you go directly to more information
about the person. I have about 34 persons which 7 columns.

Problem was that it was first build as index has the hard data and the
sheets had the links like '=index'A1' etc.
I changed that so everything on index is linked to the sheet (which
now has the hard data).

Problem (finally, sorry!):
I use '$' to keep the right cells by copying down. However; I have to
change each cell cause the sheet doesn't change. I need this:
='1'$C$1
and on the next row:
='2'$C$`1
etc

By copying Excel does:
='1'$C$1
and on the next row:
='1'$C$`1
etc

Is there a way to make Excel doing this so I don't have to change
every single cell? (34 x 7 = a lot of time!)
='1'$C$1
and on the next row:
='2'$C$`1
etc

Thanks,

Don Franco.

 

[Pete_UK]

Highlight the cells and do Find & Replace (CTRL-H), as follows:

Find What: ='1'$
Replace with: ='2'$

then click Replace All. (Type the entries exactly as above, though you
might not need the apostrophes).

Hope this helps.

Pete

 

[Guest]

It sounds like you need to use an INDIRECT function to create the target as a
concatenated string of text which incorporates the number of the current row
(or any other).

So in cell B2 enter:
=INDIRECT("'Sheet"&ROW()&"'!$C$1")
and this translates as ='Sheet2'!$C$1 (I see that your sheets are just
called "1", "2" etc, I left "Sheet" in there for clarity as to what is going
on.

If you have a row shift going on so on row 3 you need to refer to sheet 1,
just use (in B3):
=INDIRECT("'Sheet"&ROW(B1)&"'!$A$1")

Note; ROW() with no argument means ROW number of the current cell

If this works, just copy it down to all the other cells.

I have used apostrophes round the sheetname to allow for the general case
when there might be spcaes in sheet names, you can omit if this is never the
case in your example.

Hope this helps

 

[Don Franco]

Hi Adam,

I see the formula should be able to work, however; don't seem to get
it right:
B2 has the formula: =INDIRECT("'1"&ROW(b3)&"'!$b$3")
This gives the name returned on sheet '13' instead of the return on
sheet 1. Detail: it gives the return on the right cell however, just
not from the right sheet.

Something is wrong but what??