Refernce list from seperate sheet and exclude blanks

[Parmx]

Hi Guys,

I have en excel document that references a schedule produced every few days in the same format. I need excel to reference a list when the schedule is drag and dropped into a sheet. However i need it to remove the blanks in the list,

I'm using this formula, but it doesn't work when referncing the sheet "Tr7" list E7:E57, and return to active sheet A15:A65


IF(ROW()-ROW($A$15:$A$65)+1>ROWS('Tr7'!$E$7:$E$57)-COUNTBLANK('Tr7'!$E$7:$E$57),"",INDIRECT(ADDRESS(SMALL((IF('Tr7'!$E$7:$E$57<>"",ROW('Tr7'!$E$7:$E$57),ROW()+ROWS('Tr7'!$E$7:$E$57))),ROW()-ROW($A$15:$A$65)+1),COLUMN('Tr7'!$E$7:$E$57),4)))

No idea why this doesn't work, i've tried creating names for ranges also

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook?
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[avk]

Dear Parmx, Firstly i suggest you, to kindly change data structure in sheet "Tr7".
Secondly, date as per not format. Date always in date format only.
Look attach file. I have modified data structure. When maintaining data to avoid any blank row / column.

In sheet "Cycle Schedule" column "A" data extract from sheet "Tr7" "Material" using formula if, index, match, small, row.
Column "B", "C", "E" using index match criteria with "Material"
not know in column "D" & "F" which data you want to pull up.

[Parmx]

Thanks avk!