Referencing another sheet

cimirie

New Member
Joined
Sep 27, 2005
Messages
17
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,390
Messages
6,190,736
Members
453,616
Latest member
nathancook

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

Which adblocker are you using?

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
Back
Top