CODEWITHSUNDEEP
excel
karu
karu

Reputation: 45

Cell Referencing Formula

Was using this formula SheetName!CellAddress

I need to import Data that is available on other 2 sheets namely (sheet2 and sheet3) into sheet 1 in a way such that

the row values will be alternate i.e one value from sheet 2 and other value from sheet 3 respectively

Have tried like this =Sheet2!C2 =Sheet3!D2

when i dragged for other values i was get only values in the even cell like(c4,d4,c6,d6) If i change the for formula to

=Sheet2!C1 =Sheet3!D1 i was get only values in the even cell like(c3,d3,c5,d5)

But what i need is continous cells in row( c1,d1,c2,d2,c3,d3...)

So what formula i need to use for getting this result

Upvotes: 1

Views: 542

Answers (2)

lori_m
lori_m

Reputation: 5567

Here's a fairly basic method:

  1. Enter the first two formulas as Text - you can either do this by formatting the cell number as text or preceding the formula by an apostrophe.

  2. Select cells and fill down to get:

    =Sheet2!C2
=Sheet3!D2 
=Sheet2!C3
=Sheet3!D3
=Sheet2!C4
=Sheet3!D4
...

  3. Select the column and choose Data|Text to Columns|Finish to change text to values.

Upvotes: 1

ikh
ikh

Reputation: 2436

I am still not 100% clear on what the question is asking, so let me know if the below answer doesn't work for you.

It sounds like what you're looking for can be accomplished with OFFSET and clever use of IF statement.

Suppose your formulas will be in column A, starting in A2. Then enter the following formula into A2 (line split added for readability; remove it):

= IF(MOD(COUNTA(A$1:A1),2)=0, OFFSET(Sheet2!$C$1, COUNTA(A$1:A1) / 2, 0), 
    OFFSET(Sheet3!$D$1, COUNTA(A$1:A1) / 2, 0))

Then drag the formula down.

What it does:

  • MOD(COUNTA(A$1:A1),2)=0 - checks whether we're in odd row or even row.
  • COUNTA(A$1:A1)/ 2 - takes half of the number of non-empty cells immediately above the current cell.
  • OFFSET(Sheet2!$C$1, COUNTA(A$1:A1) / 2, 0) - takes the cell which is COUNTA(A$1:A1)/ 2 cells below Sheet2!$C$1.

Upvotes: 1

Related Questions