Automatic Change on Formula

[andrewmogi]

Hello friends,

I have problem with this formula =INDIRECT("'"&C$2&"'!C460"), it's working, I Use this formula to take cell C460 on another sheet named on C2, but i do have problem on making the C460 changing automaticaly, it won't change automaticly to C461 when I drag/copy this formula 1 cell down, do anyone here have the solution to make it change automatically when I Drag the formula x times down to C(460+x)?

[Pete_UK]

Try this:

=INDIRECT("'"&C$2&"'!C"&459+rows($1:1))

Hope this helps.

Pete

[FDibbins]

You didnt include any data to test on, but try something like this...
=INDIRECT("'"&C$2&"'!C"&ROW(A460))

[andrewmogi]

Both working,

Thanx for helping Friends, Very helpful

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[andrewmogi]

Here's the result example =D
anyway, when I delete some rows when using this formula =INDIRECT("'"&C$2&"'!C"&ROW(A460)), the whole data change?, how the ROW() command works actually?

[FDibbins]

ROW(A460) returns exactly that...460
as you copy it down, the 460 will increase 1 per row copied down...
row(A461) = 461 etc

row(A1) would return 1 row(A2) would return 2 etc

Pete's formula...=INDIRECT("'"&C$2&"'!C"&459+rows($1:1))
is taking 459 and adding 1 at a time...
row($1:1) = 1 +459 = 460
row($1:2) = 2 + 459 = 461
etc

My formula is working pretty much the same way...
your formula was =INDIRECT("'"&C$2&"'!C460"), so I took the 460 as a base and worked from there
=INDIRECT("'"&C$2&"'!C"&ROW(A460)) = C460
=INDIRECT("'"&C$2&"'!C"&ROW(A461)) = C461 etc

If you are deleting a row/s above 460, then you will need to adjust both of our formulas to carer for that.
Delete 1 row - change Pete's 459+rows($1:1) to 458+rows($1:1) and change my ROW(A460) to ROW(A459)
Delete 2 rows - change Pete's 459+rows($1:1) to 457+rows($1:1) and change my ROW(A460) to ROW(A458) etc

Hope that helps?

[andrewmogi]

Thanks for the explanation Fdibbins, feels great to know another way to manipulate data in excel