To Lookup Batch No's from Multiple Tabs in Another File

[fareedexcel]

Hello,

I have 2 sheets (File 1 - with Batch No and Model No of different Customers, File 2 - Model No's in different tabs)

I need to fetch the Batch No. data from File 1 corresponding to the Model No's name in File 2.

Please assist.

 

[GraH - Guido]

As I understand it, this might work:
In A1 on all sheets of File 2
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,4)
in C1 to G1 : "Customer 1" ... "Customer 5"
In C2 (drag down and to the right):
EDIT (no space required for other sheet names beside "Customer 1 " ). If you correct the sheetname to "Customer 1", then formula is generic like this.
=IFERROR(AGGREGATE(15,6,(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$B$2:$B$500")/(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$d$2:$d$500")=$A$1)),ROWS($C$2:$C2)),"")

It will give you the list of batch numbers of all the sheets "customer" for each of the model numbers. Like below


Both files need to be open though. If calculations need to work even when file 1 is open, then another solution must be found.

 

[fareedexcel]

I wasn't able to download the attached answer file of Grah. Any issue?

 

[fareedexcel]

Thanks for the help. But i need the output in this way in File 2

 

[GraH - Guido]

Perhaps I was in the edit mode and the file was actually deleted by myself.
But the basic concept is workable for you? In addition you need the full list?
Basically the same "aggregate" formula can be used. Would this do?

 

[fareedexcel]

Thanks GraH, In the Column C, Customer 1 results are not fetching from File 1. Apart from that data is capturing perfectly. Only in Column 1 data is not getting fetched.

 

[GraH - Guido]

Did you rename your sheet as suggested to "Customer 1" without the extra space?

 

[fareedexcel]

Thanks GraH, Now the data is capturing perfectly. As you mentioned there is an additional space in the Tab 1 of "Customer 1 ".

 

[fareedexcel]

When I used the same above code in my original sheet, no data is populating in my File 2 sheet. I have no clue why the data is not fetching from the original sheet. Please assist

File Name = AmjadOrder

=IFERROR(AGGREGATE(15,6,(INDIRECT("[AmjadOrder.xlsm]"&D$1&"'!$A$2:$A$500")/(INDIRECT("[AmjadOrder.xlsm]"&D$1&"'!$B$2:$B$500")=$A$1)),ROWS($D$2:$D2)),"")

 

[fareedexcel]

Now I bit changed the formula by including " ' ". Still I'm getting #NUM! Error

=(AGGREGATE(15,6,(INDIRECT("'[AmjadOrder.xlsm]"&D$1&"'!$A$2:$A$500")/(INDIRECT("'[AmjadOrder.xlsm]"&D$1&"'!$B$2:$B$500")=$A$1)),ROWS($D$2:$D2)))

 

[GraH - Guido]

hi,
At first glance, nothing seems wrong with the syntax

=AGGREGATE(15,6,(INDIRECT("'[AmjadOrder.xlsm]"&D$1&"'!$A$2:$A$500")/(INDIRECT("'[AmjadOrder.xlsm]"&D$1&"'!$B$2:$B$500")=$A$1)),ROWS($D$2:$D2))

Try the formula audit function. It might help you to detect where the error comes from. If you don't know how, then care to look at this post https://chandoo.org/forum/threads/if-formula-required.37320/#post-224310. I explained message #10 how to use formula audit.

 

[fareedexcel]

In my Original File No.1, Batch No is alphanumeric whereas in my example sheet it is only numbers may be that's the reason I'm getting error.

In this case please assist how I can rectify the error. My batch no is as follows,

ST0004
ST0005
ST0006

 

[GraH - Guido]

Indeed the formula only works for numbers. That is why it is always better to upload a file with representative data.
Back to start, without cashing in...

 

[fareedexcel]

Just now realized when I'm trying to figure out why the formula doesn't supporting. So we cannot alter the formula with text function?. I have wasted your time too.

 

[GraH - Guido]

Hi, the first formula needs a tweak like this

=IFERROR(INDEX(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$B$1:$B$500"),AGGREGATE(15,6,(ROW(INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$B$1:$B$500"))/((INDIRECT("'[Batch No''s - File 1.xlsx]"&C$1&"'!$D$1:$d$500"))=$A$1)),ROWS(C$2:C2)),1),"")

 

[fareedexcel]

Hi Grah,

I used the above formula and it is capturing the data now. when it comes to capture the complete batch list I used the same formula which you have defined. But the output seems blank

=IF(ROWS($C$2:$C2)>COUNTIF($D$2:$AB$300,">0"),"",AGGREGATE(15,6,($D$2:$AB$300)/(($D$2:$AB$300)<>""),ROWS($C$2:$C2)))

 

[GraH - Guido]

Hi Fareed, my original formula cannot work because of the same "those aren't numbers". I assumed you'd get that. I did not post a solution for the second formula, since I could not get it to navigate to the next column in the array correctly. But I know it is possible.

 

[bosco_yip]

Hi Grah,

I used the above formula and it is capturing the data now. when it comes to capture the complete batch list I used the same formula which you have defined. But the output seems blank

Hi,

I combine all your worksheets into one workbook and convert all numeric value into alphanumeric value.

1] Select A1 dropdown list for criteria

2] In "full list" B2, copied down :

=IFERROR(INDEX(C$2:G$10,MOD(ROWS($B$2:$B2)-1,COUNTIF(C$2:C$10,"?*"))+1,INT((ROWS($B$2:$B2)-1)/COUNTIF(C$2:C$10,"?*")+1)),"")

Regards
Bosco

 

[fareedexcel]

Thanks Bosco. I have tried with the formula and it is working fine. I applied the same formula in the Google Spreadsheet. It's giving me an error #Name?. When checked it's saying aggregate is an unknown function.?

 

[bosco_yip]

Thanks Bosco. I have tried with the formula and it is working fine. I applied the same formula in the Google Spreadsheet. It's giving me an error #Name?. When checked it's saying aggregate is an unknown function.?

1] Try to use SMALL+IF instead of AGGREGATE, as per attached file.

2] In the attachment "Customer list" C2, array formula copied across to G2 and all copied down :

=IFERROR(INDEX(INDIRECT("'"&C$1&"'!$B$1:$B$500"),SMALL(IF(INDIRECT("'"&C$1&"'!$D$1:$D$500")=$A$1,ROW(INDIRECT("'"&C$1&"'!$B$1:$B$500"))),ROWS($C$2:$C2))),"")

p.s.
array formula to be confirmed by pressing SHIFT+CTRL+ENTER 3 keystrokes together instead of just press ENTER

Regards
Bosco

 

[fareedexcel]

Hi Bosco,

I used the formula in the google spreadsheet,

=ArrayFormula(INDEX(INDIRECT("'"&D$2&"'!$C$5:$C$500"),SMALL(IF(INDIRECT("'"&D$2&"'!$H$5:$H$500")=$A$2,ROW(INDIRECT("'"&D$2&"'!$C$5:$C$500"))),ROWS($C$3:$C3))))

i'm getting an error saying small function has no input value data.

Regards,
Fareed

 

[bosco_yip]

Hi Bosco,
I used the formula in the google spreadsheet,
=ArrayFormula(INDEX(INDIRECT("'"&D$2&"'!$C$5:$C$500"),SMALL(IF(INDIRECT("'"&D$2&"'!$H$5:$H$500")=$A$2,ROW(INDIRECT("'"&D$2&"'!$C$5:$C$500"))),ROWS($C$3:$C3))))
i'm getting an error saying small function has no input value data.
Regards,
Fareed

I am not familiar with Google Spreadsheet formula,

but in view of Excel formula, your formula made error and I corrected in red as below :

=ArrayFormula(INDEX(INDIRECT("'"&D$2&"'!$C$5:$C$500"),SMALL(IF(INDIRECT("'"&D$2&"'!$H$5:$H$500")=$A$2,ROW(INDIRECT("'"&D$2&"'!$C$1:$C$496"))),ROWS($C$3:$C3))))

Should you still got an error, please redirect your question to the Google Sheet forum to solve.

Regards
Bosco

 

[fareedexcel]

Hi Bosco,

Thanks for the change. Code is working and the data is getting captured as per the requirement. There is some problem when the full list is getting captured.

1. In the column B, full list of the names to be captured. But if there is any space or gaps, it is missing out the data to capture.
2. As per the attached image, column E has too many entries but in the column B the data is not capturing fully.

Requirement is to capture data from all the columns from C3 to AB300. if there is no entries then it should move to the next column to capture the data.

The code used is

=IFERROR(INDEX(C$3:AB$300,MOD(ROWS($B$3:$B3)-1,COUNTIF(C$3:C$300,"?*"))+1,INT((ROWS($B$3:$B3)-1)/COUNTIF(C$3:C$300,"?*")+1)),"")

 

[fareedexcel]

Hi Bosco & Experts,

Could you please provide solution for my above query if possible.

 

[bosco_yip]

Hi Bosco & Experts,

Could you please provide solution for my above query if possible.

Hi,

1] The above mentioned formula only work for a source rectangular range without blank cells as per your post #.4 screenshot.

2] Your new requirement in post #.23 show the source rectangular range with blank cells of which can work in an expensive formula, but provided that the source data cell amount in around few hundreds only.

3] Here is an example file attached file show how the formula work for reference only :

In "Sheet 1" B3, formula copied down :

=IFERROR(INDIRECT(TEXT(RIGHT(AGGREGATE(15,6,ROW($3:$13)/1%+COLUMN(C:J)*100001/(C$3:J$13<>""),ROW(A1)),4),"R0C00"),),"")

4] However, as per your mentioned that your data were in C3:AB300, that is 15,600 cells. I am afraid the above formula unable to suit with your requirement and advice you to seek for help in VBA to solve.

Regards
Bosco