How to insert a row in worksheet A and have it auto populate in worksheet B

Copper Contributor

Good Morning, 

 

I have a workbook with two worksheets A & B.

In A I have several rows of information to copy to worksheet B.

I know how to use the = function to copy the rows from A to B but the problem comes in when i add a row to A I want that row to auto copy to B. 

Is there a way to do this?

 

I want to insert a row for A1 between rows A and AE in worksheet A and have it auto populate into worksheet B. See attachment Example.  

 

Thank you, 

19 Replies

@jessica-tls1 

 

It may be possible to do what you're asking (although I'm not sure what it is, short of writing a VBA macro)....

 

Well, there is this: if this is a one-time, one-off situation, what you could do is just enter all of your information into the first sheet, in any order at all, and then do a Data....Sort on the resulting table, sorting by Column A in Spreadsheet A....and only then use your copy formula to put things into their corresponding rows in Spreadsheet B. That would be at best a "quick and dirty" solution. But can I first ask a question or two?

 

What I'd like to ask is if you could give us a bit more background information on why you're trying to essentially have Spreadsheet B mimic Spreadsheet A (except for some of the prettier formatting features)?

 

If anything, given the nicer formatting of A, that would appear to be more of an "output" or report sheet; B, on the other hand, has more the appearance of a "raw" database.  One of the important aspects of designing a well-functioning Excel application is differentiating between Input and Output....and letting capabilities like Pivot Tables or Power Query take care of the Output at a later and separate stage.

 

Because you're entering the information (it would appear from your description) into what looks like the Output sheet, this seems backwards to me. So that's why I'm asking for a more complete description of what you're wanting to accomplish?; why you've got the two spreadsheets you have now?...what is the ultimate goal being served?

 

It's possible that re-thinking the design from the ground up will result in a far more effective end product.

@mathetes 

 

Thank you, for the reply. This is what im working on.

 

Sheet A - Input all data.

                Part of this data will be the chart ( Type Manufacturer etc. ) 

                Depending on the size of the project we will be adding rows of data to the chart. 

 

Sheet B - Output - We only want worksheet B to show the data in the chart. Because this data will be sent out to our team for the project. While the other information in A will be internal. This way our team can just print or save sheet B for external use.

 

I hope this helps with the why. 

 

@jessica-tls1 

 

Jessica -- thanks for that explanation.  I will admit to being still a bit confused. You speak of "our team" as receiving 'just the data" as it appears on Sheet B. But then refer to the information on Sheet A as "internal" ....leaving me wondering "isn't 'our team' on the same team as 'internal'?"  But I do see something I hadn't noted before, that there is different info on A than on B, and more hidden columns on B (a LOT) ....which leads me to a different suggestion altogether.

 

I'm going to suggest that you familiarize yourself with Custom Views. And I've created some using your example sheet.

 

See the attached revised version of your original example. When you open it you'll see Sheet A pretty much as you original created it. You'll see (don't be alarmed) that I removed the bottom cell, the one that spanned the entire width, with what looked to be key "footnote' kind of info. I say "Don't be alarmed" because it still is available, as a custom footer that prints whenever you print this sheet.

 

So you'll open the sheet to see pretty much what Sheet A had to begin with. Go in the main menu to View.....and scroll down to Custom Views. Pick "Team B (orig)" and you'll see what you had in your Sheet B as you uploaded it.

Then--I noticed that you'd hidden a large number of columns in Sheet B, so I created another Custom View....."Team B (full)" which includes all of those columns.

 

Now, the thing to be aware of here is that these are all simply different views--predefined views--of one and the same spreadsheet. I even changed the column headings for the different (Team A vs Team B) views, by hiding and revealing different header rows.

 

Taking this as an example, I trust you can see how a single spreadsheet can be used as both input and output....and if your "our team" that uses  B IS expected to enter data into all those hidden columns, that capability is also available. This obviates the need for any elaborate formulas or VBA macros to help keep Sheet B in sync with Sheet A. They are simply different (custom) views of the same sheet.

 

To add to all this, if there are things that you want the people in the field NOT to be able to change, you can use the Protection capability to lock cells such that only you (or some other authorized person) can change them.

 

If all this is NOT what you're actually trying to accomplish, then I apologize but I need a more complete description of what those out in the field will be doing, what the "internal" folks are doing....why different views of the one sheet wouldn't be viable for that business need.

@mathetes 

 

Thank you, for the input. 

 

Do you know what the VBA Macro code would be to have it perform the insert function i need.

Just a basic ( when i insert a row in worksheet A , then insert a row in worksheet B )

I'm sorry,@jessica-tls1 , but I don't use VBA or macros. So if you're insistent on that, I'll have to defer to some of the other experts here who do use VBA.

 

Did the custom views just not make sense to you as a solution? In retrospect, I realize I neglected to mention in my last post that you'd still need to sort the single spreadsheet on your column containing those Type designations in order to have them sorted as desired, for A1 to go in ahead of AE...but again, that's all very easy to do. And once done, the Custom View for the other users would automatically display that same order.

 

Especially if you are expecting the people in the field (the users of Sheet B), to be entering and, potentially, changing entries under the columns also visible in Sheet A, having two separate sheets [as opposed to two different views of the same sheet] could lead to inconsistencies between the data on the two sheets. That kind of inconsistency is something to be avoided, in general, in designing a spreadsheet or workbook. So again it depends on how you and the other users are going to be maintaining your two sheets.

 

I'm going to defer to others for a VBA or macro-based solution, but I want to encourage you to be open to a solution that avoids the potential of data inconsistencies.

Hi @jessica-tls1 

 

Have you considered creating a Pivot Table / Chart to use as as the 2nd sheet.  That will then automatically pick up inserted rows (by clicking refresh) and can be filtered just to display the data you need?

@jessica-tls1I agree with @Wyn Hopkins  that pivot maybe a more feasible option to explore vs VBA.

 

Just to check though, how often are the file updated? Once a month? Once a week or? If the frequency is high it'd make sense to invest some time initially to automate via VBA; else I'd recommend to go for the pivot table option. 

@crazyshoots,  @Wyn Hopkins Did the two of you look at the original workbook sample that @jessica-tls1 posted. She has a lot of empty cells in hidden columns in her Sheet B. This led me to think that input is ultimately expected on Sheet B as well as on Sheet A.

 

The "presenting problem" is one of inserting a row so as to have the various items listed in a particular order. What's never been made clear is exactly how Sheet B, which would appear to be a secondary sheet, is going to be used. It definitely appears to be more than a simple 'output." If it were that, only an "output" document, the Pivot Table would work (although, again, the multiple hidden columns that are there and NOT on Sheet A, raise questions).

 

My own sense--pending a fuller description of the full "business process" here-- remains that she'd be better off with Custom Views of a single spreadsheet, one that includes all columns that will eventually be needed, going through these steps:

  1. enter items in any order into the equivalent of Sheet A, a view that only displays those columns
  2. use Data....Sort...when needed to re-arrange the order to that desired
  3. use Custom View (any number of which could be created, as needed) to display whatever arrangement of columns is needed at the time

 

This would allow what currently is Sheet B--but would simply be a different View of the same underlying data table--to be used for simple information OR for data input.

(And, frankly, I think it would be simpler once established, than a Pivot Table with all sorts of filters.)

 

Fundamentally, though, we need a more complete description of how the input and output will be used, and as @crazyshoots has said, how frequently (and by whom) data will be updated. Without that, any "solution" may be off-track, making things more complicated than they need be.

Hi @mathetes 

 

I agree, not much point in hypothesising about a solution.  That's why I asked a question about considering a Pivot Table on sheet B and capturing all data on sheet A.

 

We await @jessica-tls1 's response...

@mathetes Hi, yes, but looking sheet A I'm thinking it could be a requirement from business point of view. I've worked in company before that insist on such formats even though it's not productive but still..

 

Let's see what Jessica's real need is and help her accordingly. :)

Maybe use 'Filter" or 'Indirect' fuction.

Here is an idea you can alter to suit your needs, maybe.

This is perfect. Thank you,

Originally I was hoping to make the updated sheet an automated process.
Example - insert line into master worksheet A and it would auto update worksheet B.
But, it appears to be way out of my league. lol
on this one you can just drag down the formulas in B and it picks up on the lines added in A which will work for what i need.

Thank you, for all the help.

Hi @jessica-tls1 

 

Attached is an alternate simple way that will will work with all versions of excel

 

It just uses INDEX and a row number to bring back the required rows

Hi @coxyrox 

 

Can you give a bit of explanation about what you're using here?

 

clipboard_image_0.png

This is very helpful. Thank you so much!
If i could ask one other thing.
If I format B with grid lines or say bold text is there a way to make them show up when lines are inserted from A? The newly inserted lines don't recognize the formatting.
Any cells you format on B should keep that formatting when new lines are added to A. You may need to highlight and format more rows on B than are currently being "used" but then when new rows are added or inserted to A they will just show up formatted on B
Also, is the intention to type any new data in on B that lines up with the values being pulled from A? If so my solution isn't ideal as if new rows are inserted (rather than added to the bottom) of sheet A then the typed in values on sheet B won't line up with the correct lines any more.

Does that make sense?

@jessica-tls1 

With the Camera tool in place, follow these steps to use it:

  1. Select the cells or range of which you want a picture taken.
  2. Click on the Camera tool. The mouse pointer changes to a large plus sign.
  3. Change to a different worksheet.
  4. Click where you want the top left-hand corner of the picture to appear. The picture is inserted as a graphic on the worksheet.