Formula when adding rows
 

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?

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" wrote in message
...
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?

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


--
MDOTJR

IT
------------------------------------------------------------------------
MDOTJR's Profile: http://www.excelforum.com/member.php...o&userid=27332
View this thread: http://www.excelforum.com/showthread...hreadid=468264

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.


--
ROCKWARRIOR
------------------------------------------------------------------------
ROCKWARRIOR's Profile: http://www.excelforum.com/member.php...o&userid=27091
View this thread: http://www.excelforum.com/showthread...hreadid=468264