1

Excel newbie here. I have 3 excel sheets. Sheet 1 contains data in columns A and B, sheet 2 contains data in column A.

What I'm trying to do is enter a formula in sheet 3 such that columns A, B and C contain the data of sheet1!A, sheet1!B and sheet2!A respectively.

If I use:

C1 =sheet1!A1:B1;sheet2!A1

I get an "#VALUE!" in cell C1, instead of values in columns A, B and C.

I found another answer which suggested using an array somewhere. I got a bit further with that. Using:

{=sheet1!A1:B1}

correctly filled in column A and B of sheet 3. However, once I add in the data from the second sheet, I get an error saying that the shape does not match. I was trying it like this:

{sheet1!A1:B1;sheet2!A1}

Am I missing an easy way to accomplish what I want to do? I'm trying to do it with a formula in column A of sheet 3 since I have to do this for a lot of rows, and referencing each column seperately takes a lot of work.

| improve this question | | | | |
  • 2
    Sheet3!A1=CHOOSE(COLUMN(),Sheet1!$A1,Sheet1!$B1,Sheet2!$A1) – Akina Aug 30 '18 at 10:00
  • This only seems to fill in column A of sheet 3. – Boris Aug 30 '18 at 10:31
  • 1
    Insert this formula to A1. Drag this selection till C1. Drag triple cells selection down as far as you need. – Akina Aug 30 '18 at 12:50
0

Have you tried CONCATENATE()?

Try putting this formula in Cell A1 on Sheet 3 and then dragging the formula down as far as you need.

=CONCATENATE(Sheet1!A1," ",Sheet1!B1," ",Sheet2!A1)

If you don't want the spaces, use this instead:

=CONCATENATE(Sheet1!A1,Sheet1!B1,Sheet2!A1)
| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.