searching a column and returning a value

[pmetzak]

Hello,

I apologize for my ignorance but I am hoping that someone will be able to tell me how I can do the following. On Sheet2, I have columns of that pick out timing information from an experiment I conducted. Each column contains only a single value but that value could be anywhere in the column between rows 1 and 850, and the location of the value changes between columns, and between datasets. I would like to write a formula on Sheet3 that will put all the timing information in the same column and in row 1 so that I can sort it more easily. Is it possible to write a formula that can perform this function, or is there another way to accomplish this?

I hope this makes sense. Please let me know if any further information is required.

Thank you very much,

Paul

[shg]

You're more likely to get a useful response if you post an example workbook and show desired results.

[Paul]

The following might be what you're after. In sheet3 cell A1 put the array formula:

=INDEX(Sheet1!A:A,MATCH(TRUE,Sheet1!A:A<>"",0))

After typing that formula you must press CTRL+SHIFT+ENTER. When done properly Excel will automatically insert braces around the formula.

Copy that across as many columns as you need. It will search each column from Sheet1 for the first cell that has a value and show it to you.