Formulas across worksheets using alternate row data

[mgerada]

Hi all

I have a challenge which I hope has a solution.

I have two worksheets (see below).
In worksheet (A) each individual item involves two rows of data.
While in worksheet (B) I want to write a formula, in say:

Cell B1 which uses the data in Worksheet (A) cell C2,
Cell B2 which uses the data in Worksheet (A) cell C4, and
Cell B3 which uses the data in Worksheet (A) cell C6 etc.

Is this possible? And if yes how so?

Look forward to a hopeful positive response.

Cheers and many thanks in advance.

mgerada

Worksheet (A)
A B C
1 Item 1 20
2 Item 1 $1.50
3 Item 2 15
4 Item 2 $2.25
5 Item 3 32
6 Item 3 $3.74

Worksheet (B)
A B
1 Item 1 (Want formula in B1 only use data in C2 in Worksheet (A))
2 Item 2 (Want formula in B2 only use data in C4 in Worksheet (A))
3 Item 3 (Want formula in B3 only use data in C6 in Worksheet (A))
4 Item 4
5 Item 5
6 Item 6

[kellyfspringer]

So, 'not sure if this is the best way to do it, but it works:

In your first row, do a direct reference to your first cell, and then in the second row, use this formula (which you can fill down):

=INDEX(WorksheetA!A:C,ROW(A1)+2,3)

[JBeaucaire]

Please Login or Register  to view this content.

It would be simplest if you also showed us what you're trying to do with this new formula on Worksheet B. Are you simply trying to lookup the price for the item in A1 and return it in B1?

=INDEX(Sheet1!C:C, MATCH(A1, Sheet1!$A:$A, 0) + 1)