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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
You must log in or register to reply here.

Similar threads

N
  • Question
Highest Value(s) displaying text
Replies
8
Views
311
Fluff
Fluff
M
  • Question
Running unique count of occurrence on table based on date
Replies
0
Views
149
Minx_Minxy
M
gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
131
gtd526
gtd526
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
115
SunnyAlv
SunnyAlv
D
  • Question
List Generator with index match within date range
Replies
2
Views
434
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,385
Messages
6,119,205
Members
448,874
Latest member
Lancelots

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