Moving a formula from one sheet to another issue [SOLVED]

[jassybun]

In the workbook, the formulas are in the same sheet as the raw data. I tried to separate them, and place the formulas into a 2nd sheet, the first row works..but the 2nd and 3rd do not.


Here is the second formula:


=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(A2)),COLUMN(A2))


that I changed to:


=INDEX(Sheet1!$A$2:$C$7, SMALL(IF(Sheet1!$B$9=Sheet1!$A$2:$A$7, ROW(Sheet1!$A$2:$A$7)-MIN(ROW(Sheet1!$A$2:$A$7))+1, ""), ROW(Sheet1!A2)),COLUMN(Sheet1!A2))


Here is the file:

https://dl.dropboxusercontent.com/u/28469410/Vlookup-return-multiple-records.xlsx

 

[Smallman]

Hi JB


Can you elaborate a bit more in the body of the post. What is the specific issue? If you outline your problem you will have a higher success rate.


Take care


Smallman


EDIT - There was no text in the body of the above post, when posting my comment above. Thanks JB.

 

[jassybun]

Sorry about that - error on my part....please see original post

 

[jassybun]

I should clarify what I am trying to do


I am trying to move A10 - C12 to another worksheet, Sheet2. I changed each part of the formula to direct it back to Sheet1.


Hope that makes sense.

 

[Smallman]

Hi JB


Go onto Sheet 2 Cell A2 and press Ctrl Shift Enter. This will add the curly brackets {} to denote an array formula. THis is the only thing you are missing. Drag formula down, formula works after this point.


Take care


Smallman

 

[jassybun]

Ugh! I totally forgot about that! Thanks so much...I don't know why I overlooked it...I thought it had to be a typo on my part!

 

[Smallman]

All good JB, it is sometimes a fresh set of eyes that are all that is needed.


Take it easy


Smallman