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 I’m looking for. Each
day the 2nd set of cells would get written over with the current data and
update the cell in B. I’m trying to eliminate manual copy and paste if I can.
Any help would be appreciated. Thanks.

--
Rags

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

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.