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
4
  • 3
    Sheet3!A1=CHOOSE(COLUMN(),Sheet1!$A1,Sheet1!$B1,Sheet2!$A1)
    – Akina
    Commented Aug 30, 2018 at 10:00
  • This only seems to fill in column A of sheet 3.
    – Boris
    Commented Aug 30, 2018 at 10:31
  • 2
    Insert this formula to A1. Drag this selection till C1. Drag triple cells selection down as far as you need.
    – Akina
    Commented Aug 30, 2018 at 12:50
  • the requirement to enter a formula once doesn't make sense to begin with. On Sheet3 make A1 =sheet1!A1, B1 =sheet1!B1 and C1 =sheet2!A1 and drag all three down. Coercing a formula into doing this for you doesn't actually even save keystrokes. It's lunacy.
    – Alex M
    Commented Jul 7, 2022 at 0:30

1 Answer 1

Reset to default
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .