The Student Room Group
Uni students - Complete our survey >>
Uni students - Complete our survey >>
Uni students - Complete our survey >>

Dit gcse

Hi,

Currently, I am doing Excel work and I'm attempting to copy and paste my table from one tab to another but whenever I paste, the information in my box changes to #REF!

I've tried searching up how to fix this but to no avail. Does anyone know how to copy the table whilst still retaining the information in my table ?
Hey, do you want to paste the info exactly as it is, or to paste it with the contents updating when the original updates?

If the first sheet contains formulae and you just want to copy the values into the second sheet, after copying, in the second sheet choose Paste, Paste Special, Paste Values.

If you want to link the cells in sheet 1 to those in sheet 2, so the values in sheet 2 update when sheet 1 updates, use Paste, Paste Special, Link.

Is this what you need?
Original post by Lysistrata
Hey, do you want to paste the info exactly as it is, or to paste it with the contents updating when the original updates?

If the first sheet contains formulae and you just want to copy the values into the second sheet, after copying, in the second sheet choose Paste, Paste Special, Paste Values.

If you want to link the cells in sheet 1 to those in sheet 2, so the values in sheet 2 update when sheet 1 updates, use Paste, Paste Special, Link.

Is this what you need?

Hi, thanks for the response. Yes, I tried that and it's working now although when I paste the table as a special, my formulas do not appear along with it. The statistics are there but just not the formulas. Any way I can change that? Again, many thanks for the response!
Original post by Jessalynne
Hi, thanks for the response. Yes, I tried that and it's working now although when I paste the table as a special, my formulas do not appear along with it. The statistics are there but just not the formulas. Any way I can change that? Again, many thanks for the response!


Yep, if you paste special \ values it will just take the results not the formulas over.

It sounds as though your issue may be absolute v relative cell addressing. That means that the cell with the formula in updates relative to the input values if you copy the formula elsewhere. That's really handy when you're copying cells down a column to, for example, give a total in column C of values in columns A and B. It's really unhelpful though in other circs. Do you want the formulas to remain the same on sheet 2, but the input values to be different?
Reply 4
Avatar for davros
davros
16
Original post by Jessalynne
Hi,

Currently, I am doing Excel work and I'm attempting to copy and paste my table from one tab to another but whenever I paste, the information in my box changes to #REF!

I've tried searching up how to fix this but to no avail. Does anyone know how to copy the table whilst still retaining the information in my table ?


The previous poster is correct in saying that Excel is probably using "relative cell addressing" for the formulas, which can cause a problem when copying. You also have a problem when copying formulas from one sheet to another if you want the target cells to reference the original sheet, because even if the formulas appear to work then they may be referencing cells in the second sheet, not the original one!

Relative addressing
================
Say you have a value in cell A1 and you set up a formula in cell C1 that just says =A1. It looks as if Excel is going to go and look up whatever value is in cell A1 and display it in C1, but that isn't how Excel thinks! Internally, Excel says "I'm in cell C1 and A1 is in the same row but 2 columns to the left, so I will create a formula that looks in the same row and 2 cells to the left and get the value". Then when you copy your formula from C1 to D1, cell D1 now shows what's in cell B1, because B1 is 2 cells to the left of B1! If B1 doesn't have any data in it, your formula will just show 0. You have an even bigger problem if you decide to copy your formula from C1 to B1 because now Excel wants to look 2 columns to the left of B1 but there isn't a column before column A, so you get a #REF! error!
You can stop things like this happening by using absolute cell addressing which means putting a dollar ($) sign before either the column letter or the row number or both in your formula like this: =$A1. This says "always look in column A for the source of the data, but leave the row as a relative address". If you now copy the formula from C1 to D1 (or even to B1) it will still say =$A1. If you copy this down to row 2 it will now say =$A2 because the row adjusts but not the column.

Copying between sheets
==================
Suppose Sheet1 cell C1 contains =A1 as before. If you copy this formula to Sheet2 cell C1 then that cell will also say =A1 which looks OK, BUT it is referencing cell A1 on Sheet2, not cell A1 on Sheet1. To include a reference to cell A1 on Sheet1, you need to include the sheet name followed by an exclamation mark in the formula like this: =Sheet1!A1. If you want absolute column addressing too, then it needs to be =Sheet1!$A1.

This sounds quite complicated but is easier to do than to explain in words :smile: If you want some examples, just google "Excel relative cell address" or "Excel how to reference another sheet".

there are various workarounds / solutions to your problem. Post back if still having difficulties :smile:
Original post by Lysistrata
Yep, if you paste special \ values it will just take the results not the formulas over.

It sounds as though your issue may be absolute v relative cell addressing. That means that the cell with the formula in updates relative to the input values if you copy the formula elsewhere. That's really handy when you're copying cells down a column to, for example, give a total in column C of values in columns A and B. It's really unhelpful though in other circs. Do you want the formulas to remain the same on sheet 2, but the input values to be different?

Hi, apologises for the late response.

I think the person below has answered the problems in regards to my excel work but yes, I wanted the formulas and values to all remain the same as well as allow all my formulas to be available when you click on the 'show all formulas' section as I needed proof that I have indeed entered the formulas manually instead entering just entering the values in.

Unfortunately, as mentioned in my initial message, it continually showed up with '#REF!' and I wasn't sure if it was just an iMac problem (as I did it on a Windows operating system which sometimes makes my work look a bit funny) or it was a problem with me copying and pasting the information into a separate sheet. As you mentioned, I pasted it as a special but to no avail, it sadly did not paste my formulas across.
Original post by Jessalynne
Hi, apologises for the late response.

I think the person below has answered the problems in regards to my excel work but yes, I wanted the formulas and values to all remain the same as well as allow all my formulas to be available when you click on the 'show all formulas' section as I needed proof that I have indeed entered the formulas manually instead entering just entering the values in.

Unfortunately, as mentioned in my initial message, it continually showed up with '#REF!' and I wasn't sure if it was just an iMac problem (as I did it on a Windows operating system which sometimes makes my work look a bit funny) or it was a problem with me copying and pasting the information into a separate sheet. As you mentioned, I pasted it as a special but to no avail, it sadly did not paste my formulas across.

Great response above explaining absolute and relative addressing. Did it work for you?

Quick Reply

Related discussions

Latest

Trending

Trending

Articles for you