Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]() 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
![]() |
|||
|
|||
![]() 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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
Adding columns (one with a formula) | New Users to Excel | |||
Adding summaries, rows and pre-population | New Users to Excel | |||
Adding a formula to a pivot table | Excel Discussion (Misc queries) | |||
adding rows | Excel Discussion (Misc queries) |