Copy & Paste Partly absolute

[adulted]

Hi all,

I have a formula ='Student-Subject-Course.tmp'!$A4 where i want to copy this to the next column and have the row number 4 change to 5 and so on...

Unfortunately, when copied from C1 to D1 the formula when copied displays as ='Student-Subject-Course.tmp'!$A4. I know abolute formula's are meant to represent the same cell unlike a relative formula that would change to it's row / column number. Any ideas??? Lots of manual editing over to cell CP1.

Thanks

 

[Zack Barresse]

Maybe something like this ...

In C1 enter:

=INDEX('Student-Subject-Course.tmp'!$A:$A,COLUMN()+1)

Copy to right as needed.


HTH

 

[adulted]

Absolute vs Relative

firefytr


You's are outstanding. Could that formula be used in the same fasion using =INDEX('Student-Subject-Course.tmp'!$1:$1,ROW()+1)

 

[Zack Barresse]

Yes. That formula will start looking at C1 and (as you drag the formula down) will look one row to the right every iteration.

The major downside to this type of formula is when using trace precedents/dependents. You will not get an arrow pointing to the cell, you must use either the formula auditor or check by hand (pressing F9 in the formula bar) to see where it points to.