×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fixing the (Row) when inserting new Row info

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

RE: Fixing the (Row) when inserting new Row info

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!

RE: Fixing the (Row) when inserting new Row info

Sorry,
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

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!

RE: Fixing the (Row) when inserting new Row info

Hey, onlyadrafter,

get back to work!

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

RE: Fixing the (Row) when inserting new Row info

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

RE: Fixing the (Row) when inserting new Row info

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!

RE: Fixing the (Row) when inserting new Row info

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.

RE: Fixing the (Row) when inserting new Row info

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?

RE: Fixing the (Row) when inserting new Row info

The formula you need is

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

RE: Fixing the (Row) when inserting new Row info

Thanks ab123456!  It works great!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close