Copy absolute formula, without changing every cell

I think this should be easier than I am making it out to be, but the answer is escaping me....


Among other things, I have a workbook with these worksheets in it: Hours, Cost, Profit, Revenue.

Columns A, B, C & D should be exactly the same on each worksheet. So, I have all the data for these columns entered into Hours, and then reference that worksheet on the other ones.

That works fine until I sort it differently and then instead of having row 2 reference row 2, it will be in row 9, etc.

Now I know I can use =+Hours!$A$2 for the absolute reference, but then i would manually have to change the reference on each cell.

SO - (finally the question) Is there a way to use the absolute reference without having to manually enter it into each cell?

Thanks!!!

Alisa

I *think* you're saying that

Please Login or Register  to view this content.

but if you sort Hours sheet such that A2 moves to A9 you get

Please Login or Register  to view this content.

and you want A2 on Cost to always = A2 on Hours, correct ?

If so then one way to do this quickly is to use the INDEX function such that

Please Login or Register  to view this content.

this can be copied down for as many rows as required and it will adapt accordingly... ie A2 will always return content from A2 on Hours sheet and A4 from A4 etc etc regardless of how the data is sorted on Hours sheet.

(another option is to utilise INDIRECT but this is a Volatile function and I would advise use of INDEX in preference to INDIRECT on that basis)