If function making a list on differnt worksheet

[ciresuark]

I am currently using =IF(Sheet1!H1="x",Sheet1!A1,"") to compile a list of items on Sheet 2 from Sheet 1. The issue I am having with this formula is where ever the information is on Sheet 1, it is put into the same row on Sheet 2.

Ex: I have an "x" in cells H2, H4, and H10, and the information is showing up on Sheet 2 in cells A2, A4, and A10.

Is there a way to tweak the formula to have the information from Sheet 1 Cells A2, A4, and A10 be in Sheet 2 Cells A1, A2, and A3?

Thank you for your help.

[Pete_UK]

Put this formula in cell I2 of Sheet1:

=IF(H2="x",MAX(I$1:I1)+1,"-")

and copy that down as far as you need to.

Then you can use this formula in cell A1 of Sheet2:

=IFERROR(INDEX(Sheet1!H:H,MATCH(ROWS($1:1),Sheet1!I:I,0)),"")

and copy that down.

Hope this helps.

Pete

[ciresuark]

Worked for me. Thanks

[Pete_UK]

Glad to hear it - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete