Problem to reference a cell or range of cells in another worksheet in the same workbook

Hi,

I've got a problem to reference a cell or range of cells in another worksheet in the same workbook.




In Sheet1 I've got text (A1:A7 and A9:A15) as well as cell A8 for which one I look in Sheet2 for a value which is in B1.

Now if I copy down I want to have:

Sheet1 A23 to return value from Sheet2 B2
Sheet1 A38 to return value from Sheet2 B3
Sheet1 A53 to return value from Sheet2 B4

Any idea on this?
Many thanks in advance

You can use this formula in cell B1 of Sheet:

=IF(ISNUMBER(A1),INDEX(Sheet2!B:B,COUNT($A$1:$A1)),"")

then copy down to the bottom of your data. Note that you may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

Hi and thanks,

Sorry but this doesn't work.

What does "... this doesn't work ..." mean? Are you getting an error message, or some obviously incorrect result?

Your example file did not tie in with your description, as you had a number in A8 and in A22 - not the cell references that you referred to.

Please submit a more accurate sample file, and indicate within there what you would like to see down column B.

Pete

Sorry my mistake all my apologies

I‘ll try to explain in an other way without table.
In Sheet1 I want have a formula in A7 which should retrieve the first result in B1 (15) from Sheet2.
Now the formula will be repeated every 13th cell(A20, A33, A46 …) whilst the result to be shown in those cells moves down in Sheet2 by only 1 cell (B2, B3, B4 …)
I hope this is more clear
Many thanks

Put this formula in B7:

=INDEX(Sheet2!$B:$B,INT((ROWS($1:1)-1)/13) +1)

changing commas and function names as required, then copy it to the cells where you want it to appear (i.e. A20, A33, A46, and so on).

Hope this helps.

Pete

Cool that's what I was looking for.

Many thanks