# Referencing another sheet

#### cimirie

##### New Member
On sheet1, I have a 5 column table whose first cell is B1. I need to copy that table to sheet2 so that I can apply a filter to it. I need to be able to do this without copy/paste as the first table will change from time to time.

On advice from one of the boards, on sheet2 I used =IF(sheet1!B1,C10,"") where C10 is the first cell in the table.

This doesn't work as it returns #VALUE!

Can somebody help me?!?

Thanks in advance.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Let's break down the formula you used here:

=IF(sheet1!B1,C10,"")

This formula is telling excel that if sheet1!B1 is true, then copy whatever is in cell C10 into the cell that you are putting the formula in otherwise leave it blank.

What you want to do is in sheet2 cell C10, put that formula in. From what you posted, I am guessing that you want the table in sheet2 to look like the one in sheet1, so try this fomula in cell C10 of sheet2: = If(sheet1!B1="", "", sheet1!B1)

What this formula is telling excel to do is to copy whatever info from B1 on sheet1 if there is anything, otherwise it will be blank.

If you need to have the table on sheet1 reproduced on sheet2, then, if the original table on sheet1 changes, the table on sheet2 remains without change, I would do a simple copy and paste. When I do this, I can change the table on sheet1, and sheet2 does not change.

Thanks to both of you for the advice. I need to clarify something. My table is 6 columns by 14 rows. When I used =IF(sheet1!B1="", "", sheet1!B1), it only pastes the first cell(B1) of sheet1 to cell C10 of sheet2. I need to transfer the entire table on sheet1 to cells C10:H23 on sheet2.

I can't just copy the entire sheet1, though, because there are other things there that I don't want on sheet2.

Thanks again for the advice

cimire:
In your first post, you say:
"On sheet1, I have a 5 column table whose first cell is B1. I need to copy that table to sheet2 so that I can apply a filter to it. I need to be able to do this without copy/paste as the first table will change from time to time."

I would like to point out that if, indeed, "...the first table will change from time to time.", then, if you use formulas similar to the one you have tried to use, any change to the table on sheet1 will immediately appear on sheet2.

So, if you are serious about no changes to sheet2, due to new changes to sheet1, the Copy and Paste that I suggested should work for you.

Replies
2
Views
428
Replies
9
Views
190
Replies
6
Views
499
Replies
1
Views
183
Replies
3
Views
185

Threads
1,203,751
Messages
6,057,147
Members
444,908
Latest member
Jayrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back