Excel help

Heedtheadvice

Now, now John!


^^^^ Cornucopia has a fairly full answer and the best method.
June you mention copying within the same workbook but on different sheets. The method also works between separate workbooks. Ensure both books are open and repeat the outlined process.

Sometimes you may want different actions such as the copied cells not to update or just to copy the formula or format et cetera. In that case you can select paste special from the menus and select the action you wish instead of a plain paste.

Ninedeuces method will copy the current contents of the first sheets cells and paste into the destination. If those source cells contain values then those values will be copied and pasted but will not reflect any subsequent updates to the source cells.
If the source cell contains a formula, that formula is copied but if the source formula is subsequently changed that change is also not reflected in the destination cell.
That general method of copy and paste can be made to do updates if instead of control+v being used to paste, right click the destination to open the paste special menu and select 'paste link'. That will then enter a formula result as described by Cornucopia.
[Some versions of Excel may need slightly different actions to get to the paste special menu but will be similar and give identical results]

System

Errr, once the copy is done and changes to the original should be reflected?

Is F9 to update relevant at all?

Should the copied data cells be write protected so they cannot be changed?

OR am I talking out of turn?

Cornucopia

Isn't F9 for a manual update of links?

NineDeuce

Oh, you just need to do '=' followed by the cell in question, just as you would if you were choosing a cell on the same tab.

agrinnall

NineDeuce wrote: »

Oh, you just need to do '=' followed by the cell in question, just as you would if you were choosing a cell on the same tab.

Your posts seem to suggest that you are guilty of very selective reading of the thread .

NineDeuce

agrinnall wrote: »

Your posts seem to suggest that you are guilty of very selective reading of the thread .

Yes, like most people, they read the OP. Otherwise people would sometimes be expected to read hundreds of posts in order to ascertain what is going on, supposedly.

AndyPix

NineDeuce wrote: »

Yes, like most people, they read the OP. Otherwise people would sometimes be expected to read hundreds of posts in order to ascertain what is going on, supposedly.

No - most people who are going to respond often read the entire thread.
Otherwise their response could be pointless

NineDeuce

AndyPix wrote: »

No - most people who are going to respond often read the entire thread.
Otherwise their response could be pointless

Most people do not sift through potentially hundreds of posts before deciding whether it is worthwhile to post. You will find duplicated information or further explanation in many posts based on the fact that these people do not probably have as much time on their hands as you....

AndyTails

Cornucopia wrote: »

No.


The "point to original cell" method works the same across sheets.

Where the reference is on another sheet, Excel will automatically create a remote reference of the form "Sheet2!A3".

Note that when working across different workbooks (as I think the OP is doing), Excel automatically creates a reference of the form "=[Book2]Sheet2!$A$3". The "$" are key here - they mean that when you copy this reference cell (e.g. from Book1, Sheet1, A3 to Book1, Sheet1, A4) it will always refer to Book2, Sheet 2, A3.


If you want the reference to update as it is copied, you need to remove the "$" signs. The "$" before A locks it to column A, and the "$" before 3 locks it to row 3. Handy tip - if you are editing the formula, you can press F4 to cycle through the different "$" options; much faster than deleting them manually.

AndyPix

NineDeuce wrote: »

Most people do not sift through potentially hundreds of posts before deciding whether it is worthwhile to post. You will find duplicated information or further explanation in many posts based on the fact that these people do not probably have as much time on their hands as you....

So why answer a question that has already been answered ?
How do you contribute to the discussion if you havent read the discussion ?


Each to their own i s'pose