-1

I have cells O4: P23 blank in 'Sheet1'. I want the contents of these cells to be populated with the contents from cells B2: C21 (same size range) from 'Sheet2'. If I copy and paste the contents from Sheet2 in to the location I want in Sheet1, it works just like I want, but if I try to use referenced formulas in O4 of Sheet1, I get the #Value! error. I have tried this already:

='Sheet2'!B2:C21

This post has a similar problem, but the solutions listed seem like they are overcomplicated and I feel like it should be much easier than this. I am using Excel 2013 and the worksheet is macro enabled, but I'm hoping to stay away from macros if possible.

Improve this question
1
  • I want to use the references instead of copy pasting because of an IF statement: IF ('Sheet1'!A1=1, 'Sheet2'!B2: C21, IF('Sheet1'!A1=2, 'Sheet2'!D2: E21, ...)) I've identified the root of the problem in the IF statement to be referencing a range of cells from another sheet, hence the title of the post. Commented Aug 25, 2017 at 22:42

1 Answer 1

Reset to default
1

In O4 enter the formula

=Sheet2!B2

No $ signs in the cell reference. This is a relative cell reference, i.e. it will change when copied across (to =Sheet2!C2) and down (to =Sheet2!B3). Copy across to P4 and down to row 23.

Note that a cell reference for a range does NOT have a space after the colon, like you wrote in your comment.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, the reason I did not recognize this solution in the first place was some of the cells being referenced were merged and led to a similar range reference problem.
Another reason to avoid merged cells.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.