Extract info from a large dynamic excel sheet.

It seems your file may be corrupt, I am unable to open it

Originally Posted by FDibbins

It seems your file may be corrupt, I am unable to open it

i have saved the file to 2003/2007 format

Nope sorry, same problem. I get an error message saying...

The file you are trying to open is in a different format than specified by the extension. Verify that the file is not corrupt and is from a trusted source before opening the file

It already was in 2003 format... .xls 2007 onwards uses .xlsx extension. I am on 2007, it can open both .xls and .xlsx files

@FDibbins

I first saved the file it then opened flawlessly

@indesh

what is the relationship between your master sheet and the detail sheets?

Indesh,

See attached.

I have created a new worksheet called PIDxx. There is a drop down that shows the unique PIDs and when you select the table update with correct data. I did the following steps.

1 Create a table from your original data (StreamingTable) so it is easy to reference the various columns in all the formula

2 The column titles in the output table (STREAMLINE NO. - which I have assumed is the Tag No. column) are automatically generated by extracting the unique ids. You need to copy this across until a blank appears, I always use conditional formatting with a gray cell so I know I have reached the end of the unique values

3 The data in the table is extracted by using index and match. The row match is done on the join of the PID & Tag no. The column match is done by the reference you have in column A against the table headers. This is the important formula to master.

=IFERROR(INDEX(StreamingTable,MATCH($B$3&E$4,StreamingTable[PIDNO]&StreamingTable[Tag No.],0),MATCH($A5,StreamingTable[#Headers],0)),"")

4 I could not find an easy way to get the unique PIDs in the data validation drop down so I had to extract the unique PID ids in column P of the Streaming Table and then used a dynamic name range to always select the correct values. A bit complicated but it works well.

I hope that helps

Regards

David

Indesh,

Glad you liked it. Please click the button to add to my reputation. Also, why don't you join my youtube channel. I will be posting some more excel videos in the near future to cover looking up data in tables and extracting unique values using array formulas.

Thank

David