Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Andy Brander
 
Posts: n/a
Default 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?
  #2   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

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?



  #3   Report Post  
MDOTJR
 
Posts: n/a
Default


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

  #4   Report Post  
ROCKWARRIOR
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to use when number of rows changes dynamically confused Excel Worksheet Functions 3 August 17th 05 04:55 PM
Adding columns (one with a formula) PaulStroik New Users to Excel 7 June 27th 05 11:32 PM
Adding summaries, rows and pre-population JFH New Users to Excel 1 May 24th 05 09:52 PM
Adding a formula to a pivot table Tony Excel Discussion (Misc queries) 2 January 20th 05 11:27 AM
adding rows Antoine Excel Discussion (Misc queries) 4 December 22nd 04 03:19 PM


All times are GMT +1. The time now is 02:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"