Jul 26 2019 09:57 AM
I use the following formula to look in the "final" tab and return "Quality Control" data as described in the formula.
=IFERROR(INDEX(final!$G$2:$G$20000,SMALL(IF("Quality Control"=final!$B$2:$B$20000,ROW(final!$G$2:$G$20000)-1,""),ROW()-1)),"")
The problem is that I have "Quality control" data in another tab as well! Is there any way to have excel look in two different tabs and combine the data?
Thanks,
Greg
Jul 27 2019 01:33 AM
Hi Greg
Would be great if you share your file . Thanks
Jul 27 2019 02:54 AM
To extract data from two ranges you may do that sequentially - when error appear on first range start selecting data from the second one. Just repeat you formula in place of "" for the first IFERROR adding to counting ROW() number of records found in first range.
For such sample
formula in G2 will be
=IFERROR(
INDEX($B$2:$B$12,
SMALL(
IF("QC"=$A$2:$A$12,
ROW($B$2:$B$12)-1,
""
),
ROW()-1)
),
IFERROR(
INDEX($E$2:$E$12,
SMALL(
IF("QC"=$D$2:$D$12,
ROW($E$2:$E$12)-1,
""
),ROW()-COUNTIF($A$2:$A$12,"QC")-1)
),
"")
)
and drag it down.
Sample file is attached.
Jul 27 2019 04:26 AM
Hi Greg,
You may also consider using Power Query and just refresh when new data is added to the source.
I attached a sample file where I created a similar situation.
In another Scenario
I can create a third table for All criteria (Data Validation)
Convert the above Query to a Function
Use Table 3 (Criteria) as a variable input to the M Query Function
Close and Load>> To destination
Using Query enables you to combine more than 2 data sources very smoothly
Using a Query Function makes it full dynamic (with Refresh)
If you want to take it to a whole next level:
Adding a little code to the Change event of the cell having Data Validation, Can automatically refresh the Query
Hope that Helps
Nabil Mourad