Cell Reference Issue...

[verbalstorm]

Hey Guys,

I have a sheet that I have been running to keep up with our employees. Each employee has their own individual sheet which tracks there arrival, tardies, uniform, etc.... Then there is a summary sheet that references all of the employees and their results each week. We use a formula with the = sign and that respective cell on their individual sheet to copy their data to summary sheet. We copy the week so as to not have to type info in each week but when the copy is done, it adds cells to the reference so it is off by seven cells. Example: ='Ms. Tina'!G130 references Ms. Tina's attendance on the summary sheet on feb 14, 2014 and ='Ms. Tina'!G131 which is her lunch. But when I copy all the teachers from feb 14, 2014 to feb 21, 2014, it references ='Ms. Tina'!G137 instead of ='Ms. Tina'!G144. Is there a way we can add a whole range of cells by 7 that references cells from another worksheet? Please advise....

[daffodil11]

Where is the cell you are copying to in relationship to the cell you are copying from?

You can use the XL4 backdoor with Get.Cell to pull the formula of another cell, but you need to know the positional relationship between the cell and the formula you design.

For example, one variety of the GET.CELL might pull from cells directly above, and another might be designed to pull from a cell that's three cells above and one to the left.

Using GET.CELL you can write a formula that finds the first number of the formula you use to separate out the whole number, at 7 to it, and put it back together as a cell reference.

So that =Bob!A123 becomes 123 which becomes 130, which concatenates to the orginal string as =Bob!A130