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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You must log in or register to reply here.

Similar threads

E
  • Locked
  • Question Question
Excel formjlailter data to be analysed
Replies
1
Views
144
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
291
Jeffrey Mahoney
Jeffrey Mahoney
T
  • Question Question
SUMPRODUCT Over Multiple Columns
Replies
2
Views
266
thewiseguy
T
L
  • Solved
Updating data in cell, run the macro again and it should not happen.
2 3 4
Replies
34
Views
1K
mumps
mumps
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
194
Pbobas
P

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

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