Formula when adding rows

[Guest]

I have a simple table where data is gathered from another worksheet (lets
call it WS2), the formulas look something like:
Col A Col B Col C Col D
J Smith =WS2!G4 =WS2!G7 =WS2!G10
B Jones =WS2!G5 =WS2!G8 =WS2!G11
H Walsh =WS2!G6 =WS2!G9 =WS2!G12

When I duplicate a row (in order to create a new one) I was expecting the
references in the formula to automatically adjust, e.g. the cell references
would become 4,5,6,7 in column B, 8,9,10,11 in Col C and 12,13,14,15 in Col
D. But they don't - the cell references in Col B are 4,5,6,6 and Col C & D
are unchanged.

I am sure they automatically adjusted in another spreadsheet but I cannot
figure out what I am doing differently. The above is an example, the
actual spreadsheet is very large hence me wanting to avoid manually changing
all the formule. Can anyone help?

 

[Earl Kiosterud]

Andy,

No way. If you're copying the H Walsh row down, =WS2!G6 should definitely
become G7. Unless, by chance, it's really =WS2!$G$6, or G$6.

 

[MDOTJR]

copy and paste the row do not just add one.. that way all the formula
will be transfer acordingly..

 

[ROCKWARRIOR]

I'm not sure this will help. I'm fairly new to Excell & self taught.
may not have understood your question completely. Anyway ...
discovered this by accident when trying to repeat information from wb
into wb2 automatically.

My formula originally read as follows
=REPT('[workbook.xls]Sheet1'!$A$272,1) but it would only copy/paste o
wb2 exacly, instead of A272..A273..

I found that if you delete the $ in the formula on both sides of the
- so now it looks like =REPT('[workbook.xls]Sheet1'!A272,1) - it wil
still repeat the cell from wb1 to wb2.. and nowyou can copy the row i
wb2 and paste the formula to the remaining rows in wb2.

I have a haunting feeling that there may be some problem with this whe
adding/deleting rows from one or both workbooks however. So youmay wan
to get additional input... in fact, I will be looking for other reply
to your question.

Hope this helps