Fixing the (Row) when inserting new Row info
Fixing the (Row) when inserting new Row info
(OP)
Help, “Excel Rookie”
I am working on an Excel 2000 spreadsheet list; Sheet 1 cell will feed Sheet 2 and Sheet 3.
Simply – Sheet 2 cell fed by (=Sheet 1 cell) – Sheet 3 cell fed by (=Sheet 1 cell)
Sheet 1 is a list 6000+/- (row cells) long.
Sheet 1 is made up of shorter groups.
The groups run (1-001 thru 1-250) & (2-001 thru 2-160) and on.
Sheet 1 looks like this.
______A_________
1___1-001__
2___1-002__
3___1-003__
4___2-001__
5___2-002__
6___3-001__
Problem
When I insert new updated information in Sheet 1, say (inserting 1-004 at (A4) above) Sheet 2 does not read new information. Sheet 2 cell formula changes from [=A4 to =A5] and reads (2-001). I want Sheet 2 to read new information (1-004).
I want to be able to (add & subtract) on Sheet 1 and have Sheet 2 and Sheet 3 update new info..
Is this possible?
Anyone?
I have been searching Excel reference books. What function should I use?
After I get past this I will be looking for a way to have sheet 3 automatically know when the group changes are taking place.
Any help appreciated.
Thanks
I am working on an Excel 2000 spreadsheet list; Sheet 1 cell will feed Sheet 2 and Sheet 3.
Simply – Sheet 2 cell fed by (=Sheet 1 cell) – Sheet 3 cell fed by (=Sheet 1 cell)
Sheet 1 is a list 6000+/- (row cells) long.
Sheet 1 is made up of shorter groups.
The groups run (1-001 thru 1-250) & (2-001 thru 2-160) and on.
Sheet 1 looks like this.
______A_________
1___1-001__
2___1-002__
3___1-003__
4___2-001__
5___2-002__
6___3-001__
Problem
When I insert new updated information in Sheet 1, say (inserting 1-004 at (A4) above) Sheet 2 does not read new information. Sheet 2 cell formula changes from [=A4 to =A5] and reads (2-001). I want Sheet 2 to read new information (1-004).
I want to be able to (add & subtract) on Sheet 1 and have Sheet 2 and Sheet 3 update new info..
Is this possible?
Anyone?
I have been searching Excel reference books. What function should I use?
After I get past this I will be looking for a way to have sheet 3 automatically know when the group changes are taking place.
Any help appreciated.
Thanks





RE: Fixing the (Row) when inserting new Row info
On sheet two in cell a1 type this
=CELL("CONTENTS",Sheet1!A1).
and then drag the cell down to fill the rest up.
This is assuming you want all cell A1 on sheet 1 to equal A1 on sheet 2 and sheet 3.
Hope this helps.
maybe only a drafter
but the best user here!
RE: Fixing the (Row) when inserting new Row info
this won't work as I was inserting cells in the wrong sheet.
SORRY!
maybe only a drafter
but the best user here!
RE: Fixing the (Row) when inserting new Row info
I think I have a solution, although you will need to change the spreadsheet a little.
Lets see if you can follow this.
On Sheet 1 Insert a Row at '1', and a column at 'A' so the first column and row are blank.
In 'A2' type 0. In 'A3' create a formula ='A1'+1, copy this down as far as you want to go.
In 'B1' type 0. In 'C1' create a formula = 'B1'+1, copy this across as far as you want to go.
In 'B' type the following (carefully)
=CELL("CONTENTS",OFFSET(Sheet1!$A$1,$A2,B$1))
copy this down and across as fas as you want to go.
Inserting cells, rows or columns, in sheet 1, should have no effect on sheet 2.
Hope ths is helpful!
maybe only a drafter
but the best user here!
RE: Fixing the (Row) when inserting new Row info
get back to work!
kidding,
Great handle, first laugh of the morning - thanks
tg
RE: Fixing the (Row) when inserting new Row info
=OFFSET(Sheet1!$A$1,ROW()-1,0)
and then copy to as many cells as you require.
Inserting additional rows on sheet1 would then be reflected also on sheet2.
This formula places zero's on sheet2 whenever an empty cell is on sheet1, if this is a problem you can either tools >> options >> uncheck zero values on view tab or if you prefer use an if statement in the formula to display zeros as emptycell.
Hope this does the trick
RE: Fixing the (Row) when inserting new Row info
This only works for Column A, if you copy these cells over columns B,C etc you still get the values from Column A in sheet 1.
maybe only a drafter
but the best user here!
RE: Fixing the (Row) when inserting new Row info
=OFFSET(Sheet1!A$1,ROW()-1,0)
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Fixing the (Row) when inserting new Row info
RE: Fixing the (Row) when inserting new Row info
=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)
RE: Fixing the (Row) when inserting new Row info