Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copying Data Between Cells
I have spreadsheet A and spreadsheet B. I update A each day and need to
copy that info to B as well. I set up a link from A to B and it worked fine. My problem is that A gets updated each day in a new set of cells so the link from the previous day no longer works. I was thinking it might also work if I can update A and have Excel, at the same time, automatically copy my update to a 2nd set of cells in A. I can then link this 2nd set of cells to B and get the info Im looking for. Each day the 2nd set of cells would get written over with the current data and update the cell in B. Im trying to eliminate manual copy and paste if I can. Any help would be appreciated. Thanks. -- Rags |
#2
|
|||
|
|||
Maybe you could give us some more details on
a) what the data in A looks like b) where new data goes each day c) what formulas you have in B that attempt to reference the new data in A It might be possible to devise formulas for B that survive changes in the data in A. Otherwise I would suggest a macro to update B. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Not sure what the problem is, so let me guess.
Each day you update your primary spreadsheet, the new data goes into new cells. By this you mean you add a new row or a new Coloumn? If this is the case, you have a couple of options. Your idea should work fine, copying the data to a transitional space, and link to that. To avoid manual stuff, build a macro. You can use things like [end] [down] ,[home], etc. to force it to a starting point, and then to go to the end of your data set. Just start the macro recorder, go to some random place on the spreadsheet, then navigate to your data in a way that does not need interaction. Ctrl home, end down, end down, end down, always gets you to the bottom of your data set, no matter how long it is for instance. Then select the line, copy, Jump to your destination, paste., stop macro. You could even have it close a and open B for you. Another option would be to use a named range. In A, select today's data, and give it a range name like "current data." Then, in B, set up your links to use that range name rather then the cell reference. Then, all you have to do is create a little macro in A that for instance, selects the current line, and gives it that name, or use the technique above, to navigate to the end or beginning of your data, and slect the line, give it the name. Another even easier option, (depending on how much data we are talking about, ) would be to use Vlookup or Hlookup. In addition to all of your other data, put a row or coloum that can be used for "choose me." Fill it full of zeros, but put a "1" by the coloum you want the "B" sheet to use. Use Vlookup on the "B" sheet to get the data, make sure that the range you select is far bigger then your current data, so as your data grows, itis stillinrange of the vlookup. then when you add new data, all you have to do is zero out the previous "1", put a one in place for the new data, and you are all set. Hope one of these helps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
how to delete contents of cells having specific data | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel | |||
Count cells with data | New Users to Excel |