Formula when adding rows

[Andy Brander]

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?

[MDOTJR]

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

[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.
--
Earl Kiosterud
www.smokeylake.com

"Andy Brander" <[email protected]> wrote in message
news:[email protected]...
>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?

[ROCKWARRIOR]

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

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

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

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

Hope this helps.