Fixing the (Row) when inserting new Row info

[makworks]

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

 

[onlyadrafter]

Hi,

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!

 

[onlyadrafter]

Sorry,
this won't work as I was inserting cells in the wrong sheet.

SORRY!


maybe only a drafter
but the best user here!

 

[onlyadrafter]

Right, Here we go again.

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!

 

[trainguy]

Hey, onlyadrafter,

get back to work!

kidding,
Great handle, first laugh of the morning - thanks
tg

 

[ab123456]

On sheet2 enter the formula

=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

 

[onlyadrafter]

to ab123456

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!

 

[joerd]

Yup, then you need to change $A$1 to A$1:

=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.

 

[IrogSinta]

I like the latest solution. I actually need this for what I'm trying to do but I need to insert columns too without changing sheet 2. Is it possible to have it do the same thing with both rows and columns?

 

[ab123456]

The formula you need is

=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)

 

[IrogSinta]

Thanks ab123456! It works great!