How to pull data from multiple worksheets into one final

I

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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You must log in or register to reply here.

Similar threads

E
  • Locked
  • Question Question
Excel formjlailter data to be analysed
Replies
1
Views
152
Fluff
Fluff
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
311
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
Calculate annual sales per country
Replies
5
Views
160
AhoyNC
A
T
  • Question Question
SUMPRODUCT Over Multiple Columns
Replies
2
Views
274
thewiseguy
T
L
  • Question Question
Help with formula to keep leading zero in cell - cant format as date
Replies
4
Views
70
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,765
Messages
6,180,845
Members
453,001
Latest member
coulombevin

Share this page

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
Back
Top