How to pull data from multiple worksheets into one final

IrisDiane

New Member
Joined
Dec 11, 2020
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
Hello! Im trying to sort data in my workbook from across multiple worksheets into a single workbook

P Helper(1479).xlsx
ABCDEFGHIJKL
1Number: 355Name:
2FPCMCMUMJMC2MKMAME DATEAI
3GrapeFree0.10.81.10.124.500Jan-30-21Good
4GrapeFree0.10.81.10.124.500Jan-30-21Good
5MangoFree0.92.16.59.18.75.50.3Jan-30-21Poor
6LemonFree0.552.99.95.61.98.90.3Jan-30-21Okay
7OrangeFinal0.28.24.20.12.35.52.2Jan-19-21Fine
8GrapeFree0.10.81.10.124.500Feb-22-21Good
9LemonFree0.552.99.95.61.98.90.3Feb-22-21Okay
10LimeFinal0.239.19.17.145.15.18.9Mar-20-21Poor
11AppleFinal0.010.20.10.10.050.050.1Mar-20-21Great
355
Cell Formulas
RangeFormula
C3:C11C3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,2,FALSE),"")
D3:D11D3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,4,FALSE),"")
E3:E11E3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,5,FALSE),"")
F3:F11F3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,6,FALSE),"")
G3:G11G3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,7,FALSE),"")
H3:H11H3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,8,FALSE),"")
I3:I11I3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,9,FALSE),"")
J3:J11J3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,10,FALSE),"")
L3:L11L3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,3,FALSE),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H4:J167Cell Valuebetween 0 and 0.05textNO
G4:G167Cell Valuebetween 0 and 0.05textNO
F4:F167Cell Valuebetween 0 and 0.05textNO
E4:E167Cell Valuebetween 0 and 0.05textNO
D4:D167Cell Valuebetween 0 and 0.05textNO
B4:B167Cell Valueending with "*"textNO
B4:B167Cell Valueending with "'"textNO
B4:B167Cell Valueending with ","textNO
B4:B167Cell Valueending with "."textNO
D3:J3Cell Valuebetween 0 and 0.05textNO
B3Cell Valueending with "*"textNO
B3Cell Valueending with "'"textNO
B3Cell Valueending with ","textNO
B3Cell Valueending with "."textNO
Cells with Data Validation
CellAllowCriteria
B3:B11List=Data!$B$2:$B$20


P Helper(1479).xlsx
ABCDEFGHIJKL
1Number: 407Name:
2FPCMCMUMJMCMKMAME DATEAI
3GrapeFree0.10.81.10.124.500Jan-30-21Good
4GrapeFree0.10.81.10.124.500Jan-30-21Good
5MangoFree0.92.16.59.18.75.50.3Jan-30-21Poor
6LemonFree0.552.99.95.61.98.90.3Jan-30-21Okay
7LimeFinal0.239.19.17.145.15.18.9Jan-19-21Poor
8StrawberryFinal0.13.17.56.52.32.95.1Feb-22-21Poor
9TomatoFree1.53.27.34.57.42.33.2Feb-22-21Poor
10LimeFinal0.239.19.17.145.15.18.9Mar-20-21Poor
11AppleFinal0.010.20.10.10.050.050.1Jan-21-21Great
12AppleFinal0.010.20.10.10.050.050.1Jan-15-21Great
407
Cell Formulas
RangeFormula
C3:C12C3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,2,FALSE),"")
D3:D12D3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,4,FALSE),"")
E3:E12E3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,5,FALSE),"")
F3:F12F3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,6,FALSE),"")
G3:G12G3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,7,FALSE),"")
H3:H12H3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,8,FALSE),"")
I3:I12I3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,9,FALSE),"")
J3:J12J3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,10,FALSE),"")
L3:L12L3=IFERROR(VLOOKUP(B3,Data!B$2:M$13,3,FALSE),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H4:J10Cell Valuebetween 0 and 0.05textNO
G4:G10Cell Valuebetween 0 and 0.05textNO
F4:F10Cell Valuebetween 0 and 0.05textNO
E4:E10Cell Valuebetween 0 and 0.05textNO
D4:D10Cell Valuebetween 0 and 0.05textNO
B4:B10Cell Valueending with "*"textNO
B4:B10Cell Valueending with "'"textNO
B4:B10Cell Valueending with ","textNO
B4:B10Cell Valueending with "."textNO
D3:J3Cell Valuebetween 0 and 0.05textNO
B3Cell Valueending with "*"textNO
B3Cell Valueending with "'"textNO
B3Cell Valueending with ","textNO
B3Cell Valueending with "."textNO
H11:J167Cell Valuebetween 0 and 0.05textNO
G11:G167Cell Valuebetween 0 and 0.05textNO
F11:F167Cell Valuebetween 0 and 0.05textNO
E11:E167Cell Valuebetween 0 and 0.05textNO
D11:D167Cell Valuebetween 0 and 0.05textNO
B11:B167Cell Valueending with "*"textNO
B11:B167Cell Valueending with "'"textNO
B11:B167Cell Valueending with ","textNO
B11:B167Cell Valueending with "."textNO
Cells with Data Validation
CellAllowCriteria
B3:B12List=Data!$B$2:$B$20


These are the sheets I am pulling data from (Plus an additional 50 or so)

P Helper(1479).xlsx
ABCDEFGH
1Apple
2
3
4GrowerDate UsedActive IngredientStatus
5355Mar-20-21GreatFinal
6407Jan-21-21Great Final
7407Jan-15-21GreatFinal
8405Mar-20-21GreatFinal
Desired Result
Cell Formulas
RangeFormula
C5C5=VLOOKUP(A$1,'355'!B$3:L$11,10,FALSE)
E5E5=VLOOKUP(A$1,'355'!B$3:L$11,11,FALSE)
G5G5=VLOOKUP(A$1,'355'!B$3:L$11,2,FALSE)
Cells with Data Validation
CellAllowCriteria
A1:C2List=Data!$B$2:$B$13


This is the result im hoping to get (or something similar) Using a drop down menu to select the fruit, and any worksheet that mentions that fruit gives me the associated number date and active ingredient.

Any help would be greatly apprieciated!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,317
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top