I have excel 2007. I have a workbook with 2 tabs set up as follows:
  • Tab A
  • Tab B

Tab A contains a column with the source data, which will be constantly overwritten

Tab B has a row which references the data in tab A

I would like to copy the reference formula in Tab B down for say 100 rows, but when I use the fill tool, it automatically moves the reference cell up one. I want it to remain the same as the original.

E.g
Tab B, Row 1

ColA = Tab A, Row 1
ColB = Tab A, Row 2
ColC = Tab A, Row 3

When I fill copy the above; on Tab B, Row 2, the formula changes to ColA = Tab A, Row 2, ColB = Tab A, Row 3 etc. I would like them to remain as ColA = Row 1, ColB = Row 2 etc.

I will then use Tab A to input each rows data in Tab B, having converted the previous row into just text so it doesn't change with each update.
Hope this makes sense.