Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Automatically Add Rows/ Values From A Linked Worksheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi-

I am brand new to Mr. Excel and would love some advice.

I searched the boards pretty extensively but could not find what I am looking for...I apologize if this is a duplicate.

I am using Excel 2007

How do you automatically add rows and update values for cells to a linked worksheet in which rows have been added? For example: Sheet 1, columns A & B are linked to Sheet 2, columns A & B. Sheet 2 has values in A1:A5 & B1:B5 and Sheet 1, since it is linked, has the same info. I want to add a row in between 3 & 4 on Sheet 2 and want Sheet 1 to automatically add the same row and update the value of the cell in column A & B.

Any help is greatly appreciated!

John


View Answers     

Similar Excel Tutorials

Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Sum Values from Every X Number of Rows in Excel
Add values from every x number of rows in Excel. For instance, add together every other value in a list, or every 5 ...
Convert Numbers into True and False in Excel
How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very important when working with mo ...
Quickly Switch the Columns and Rows of a Data Set in Excel
How to switch a data set in Excel so that the columns become rows and the rows become columns. This will save you ...
How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...
Manually Calculate Formulas and Functions in Excel
How to force Excel to recalculate all formulas and functions without editing or entering any data. This works grea ...

Helpful Excel Macros

Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics







Hello,

Is there a way to create a second worksheet that is based on data from an original sheet that will automatically update when you ADD ROWS to the original sheet?

Linking values between sheets is easy... but I need to be able to set a designated area on my A sheet that I can add rows into which will automatically be added to my B sheet.

Does anyone know of a function that I can apply for this?


Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.


Hi all,

I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!!

I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated.

I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if "ALL" is typed in the date fields, it pulls over ALL the data. (Or something similar)

Any help you can give would be much appreciated.

Cheers,

Kevin


I have sheet with rows and each row has formulas based on cells in that row.
I would like to have a new row automatically inserted with the formulas
updating to reference the cells in the new row. Let's say I start with 50
rows and after they are filled up, a new row is added automatically? How do I
do this?



Hello, what I would like to do is take a portion of a sheet..... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the "proposal creation" sheet I want it to mirror only a specific cells to another sheet that is "salesman copy" that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the "salesman copy" which is what we will print for clients. Thank you.

Hi peeps

I want to combine data from several worksheets into one worksheet.

For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).

I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.

I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?

Any help will be beautiful.

Cheers



Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True


By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?


Hi Excel experts,
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.

Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.

Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.

Can anyone assist?

Thanks!


Hey

I have three columns containing last name values from 3 different sources that I've brought into one sheet. I am trying to find a way to compare all three values to establish if they are the same.

Of the three values there is no master, Each value may be different. So essentially it would be like doing =IF(a1=a2=a3,"match", "nomatch")
But of course this isn't possible.

I don't care about obtaining detail about which value matches which. I just need true or false response as to whether all three values are the same or not.

Thanks for any help anyone can provide.

Aaz


Hello,

I am copying charts to display the same information for different regions. When I work on the copied region, I find it a cumbersome task to go through each charts source data and change the cell references to the different region. Essentially I am doing a trending analysis for each region, with a region having its own sheet with 5 charts per sheet. What I have done is copy the original sheet and am updating the sheet for another regions source data by going to the source data. The source data is all in the same row/column format, but each regions source data has its own sheet. Is there a more automatic way to do this? Perhaps a way to update all 5 charts references at the same time.


Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?


I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook.

Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff.

Each worksheet will be identical, using columns A-I with row 1 having the headings:

Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell).

There will be a varying number of rows in each of the individual sheets.

If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.

If anyone can help it would really cut down the time I spend collating these stats every day!


Is it possible to apply a filter to multiple sheets within the same Excel workbook, using the same filter criteria?

For example, suppose you have tables of data on 3 different tabs, all having the same headings. Suppose one of the headings is "gender". If you apply a filter to the table on Sheet 1, to only show the rows containing the word "female", is it possible to have the tables on Sheets 2 and 3 automatically update to only show the "female" rows? Or would you have to manually update the filter criteria in Sheets 2 and 3?

I hope this makes sense. Thank you in advance for any help.


Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag


Hi!

I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.

However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.

I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?

Any advice is higly appreciated.


Hi everyone....this is my first post here ....and not my last I believe!

My question I believe is easily solvable for you cracks of excel.
I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.
On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.
I created an example on a worksheet attached just for you to understand.
I apreciate all the help I can get.
Thank you so much.
RG


I was wondering if anyone could help me out please?!?!

I need to have a cell on Sheet2 display the TEXT from a cell on Sheet1, is his possible??

Example:

Sheet1, Cell A1 contains a clients name and I would like to have Sheet2, Sheet3, Sheet4 etc... Display that clients name in a cell of my choosing (could be a different cell on each Sheet) automatically after entering it once on Sheet1, cell A1.

I thought this was possible but I can't seem to figure it out. I haven't done any real Excel work in quite a long time but I thought I had done this before a long time ago!! Any help would be GREATLY appreciated!! Thanks in advance...

P.S. If I use autosum to do this it just displays the number 0, obviously, since it is trying to add numbers...

I uploaded a copy of the spreadsheet, What I would like to happen is have the text from Sheet1,D4 automatically be placed in Sheet2,A1 and Sheet3, B2. Hope this helps

-Aric


I am creating a data sheet to be completed by other users. I would like to
format the text cells (name, etc) to have text entered as uppercase
automatically although the user might use title or lower case.

UPPER function cannot make cell look at itself and perform the function

Excel 2003



I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.




Hello everybody,

We all know we can change the source of a link of the workbook by clicking Edit>Links>Source>change source but that will change the source of all workbook formulas which are linked to a certain file, what about if i need to change the source of a single sheet? is this possible?

I appreciate your support.Thank you !


This is probably an easy question for most of you, but it's killing me. I tried searching before I posted this, but the suggestions given didn't seem to work for me.

I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:

Sheet 1
Column A has a long list of code type 1s
Column E has a long list of code type 2s

Sheet 2
Cell C2 has code 1
Cell E2 needs code 2

I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:

=INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)

But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.


Hi there. I'm using Excel 2007 and often use filters to find inconsistent data (mis-spellings, etc.) and then clean up the data using the fill handle to fill in correctly formatted values. I'm finding that, with a simple filter on, dragging over cells hidden by the filter changes the values in the hidden cells too. This is something I don't remember from my last version of Excel. I'm wondering if I've mis-remembered how this control works, or if there is a setting I can't find.

My goal is to drag and fill (or paste) in a colum with a range of rows wiht some hidden (filtered out) rows, and have the filtered out data be unaffected, if that makes sense.

Thanks!

T


Hi,

I am entering lots of family history data into a spreadsheet. At the simplest I have columns (in cells A1 B1 C1) the headings, Surname, Forename, Year. Right now, I enter in cells A2 B2 and C2 say: Smith <Tab> John <Tab> 1555 <Enter, move mouse to the A column in the next row down). What I want to happen is when I have entered the last data in a row and pressed <Enter> I move automatically to the A-column in the next row down.

Is this possible?

Regards and a Merry Christmas to all

Wibs


Hello.

I have two huge excel files, with many rows and columns, they "should" look the same.

But I need to find out if anything has been added or removed from the second one compared to the first one.

Which is the easiest way to accomplish this?

Searched this forum and Google but couldn't find what i was looking for.

Thanks in advance!


Column 1 has roughly 20 rows of information. Cell C1 has the formula =A1.

Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...

I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values

Thanks