Matching cells and copying the whole row

[taibe]

Hi All,

Im sure this has been asked a million times, and me being a noobie, I cant seem to find the answer online.

Im looking to create a file which has 2 stages, first being where the data is entered and recorded in one worksheet. Second, the data being pulled out according to requirement and displayed in graph & tables.

I have completed the first part using macro, but stuck on the second part. I have found some formulas online, but cant seem to get the formula right.

The user inputs the data on 'Comments' worksheet, clicking 'Save Data' the data is saved in 'Data worksheet. In the 'Properties' worksheet, depending on the user selection in F1, the data is copied from the 'Data' worksheet.

Copy Comment Cards.xlsx

If anyone can help, it would be greatly appreciated.

[benishiryo]

hi taibe, welcome to the forum. just fix the row reference of the INDEX array & since this is an array formula, press CTRL + SHIFT + ENTER after entering the formula & you'll be fine.
=IF(ROWS(A$2:A2)<=$H$1,INDEX(Data!A$2:A$50,SMALL(IF($F$1=Data!$A$2:$A$50,ROW(Data!$A$2:$A$50)-ROW(Data!$A$2)+1),ROWS(A$2:A2))),"")

[taibe]

Wow, so simple answer.

BTW, why CTRL + SHIFT + ENTER, what does that do?

Sorry for the noobie question.

All solved, thanks a million.

[benishiryo]

you're very welcome. they are for array formulas. despite being extremely useful, do note that such formulas slow down Excel. here's a good read on it:
http://www.cpearson.com/excel/arrayformulas.aspx