Help!!!!

euan_walker

New Member
Joined
Sep 17, 2015
Messages
11
I have a large spreadsheet of information, with an extract as per below.


A
B
C
D
E
F
G
1
Make
Model
Manufactured
Purchased
Fuel
Value
Status
2
Ford
Focus
2013
2013
Petrol
Not for Sale
Ford
3
Nissan
Note
2013
2015
Petrol
For Sale
Nissan
4
Nissan
Micra
2014
2014
Petrol
For Sale
Nissan
5
Ford
Mondeo
2012
2013
Diesel
For Sale
Ford
6
Ford
Mondeo
2011
2013
Petrol
For Sale
Ford
7
Ford
Fiesta
2015
2014
Petrol
Not for Sale
Ford
8
Kia
Sportage
2014
2015
Diesel
Sold
Kia
9
Fiat
500L
2014
2014
Petrol
Not for Sale
Fiat
10
Audi
A3
2013
2013
Petrol
Not for Sale
Audi
11
Audi
A3
2015
2016
Petrol
Not for Sale
Audi
12
Kia
Sportage
2015
2015
Diesel
For Sale
Kia
13
Ford
Focus
2014
2015
Diesel
Not for Sale
Ford
14
Honda
Accord
2015
2015
Diesel
Sold
Honda

<tbody>
</tbody>

Is there a solution within excel that will allow me to take certain area of this information from one work sheet and create a continuous list in another worksheet of the information without distorting the original spreadsheet and not creating gaps in the new worksheet.

In worksheet 2, I am looking to create a drop down list in cell B2 and C2 then within Cells A2:D5 it would bring back information from sheet 1 dependant on my selection as per below.


A
B
C
D
1
Selection
Ford
Status
Not For Sale
2
Make
Model
Fuel
Status
3
Ford
Focus
Petrol
Not for Sale
4
Ford
Fiesta
Petrol
Not for Sale
5
Ford
Focus
Diesel
Not for Sale

<tbody>
</tbody>

I would assume that a VLOOKUP would be the answer however I think that there is multiple makes the same this keeps returning an error

Any assistance would be greatly appreciated

Many Thanks
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jessi81882

New Member
Joined
Jul 20, 2016
Messages
26
What about using a Pivot Table? Search on this site for how to set up a dynamic range on which to base the pivot table
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
If neither pivot tables nor filters are acceptable, try this:

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Make</td><td style=";">Model</td><td style=";">Manufactured</td><td style=";">Purchased</td><td style=";">Fuel</td><td style=";">Status</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Selection</td><td style=";">Ford</td><td style=";">Status</td><td style=";">Not For Sale</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Ford</td><td style=";">Focus</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">2013</td><td style=";">Petrol</td><td style=";">Not for Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Make</td><td style=";">Model</td><td style=";">Fuel</td><td style=";">Status</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Nissan</td><td style=";">Note</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">2015</td><td style=";">Petrol</td><td style=";">For Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ford</td><td style=";">Focus</td><td style=";">Petrol</td><td style=";">Not for Sale</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Nissan</td><td style=";">Micra</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">2014</td><td style=";">Petrol</td><td style=";">For Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ford</td><td style=";">Fiesta</td><td style=";">Petrol</td><td style=";">Not for Sale</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Ford</td><td style=";">Mondeo</td><td style="text-align: right;;">2012</td><td style="text-align: right;;">2013</td><td style=";">Diesel</td><td style=";">For Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ford</td><td style=";">Focus</td><td style=";">Diesel</td><td style=";">Not for Sale</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Ford</td><td style=";">Mondeo</td><td style="text-align: right;;">2011</td><td style="text-align: right;;">2013</td><td style=";">Petrol</td><td style=";">For Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Ford</td><td style=";">Fiesta</td><td style="text-align: right;;">2015</td><td style="text-align: right;;">2014</td><td style=";">Petrol</td><td style=";">Not for Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Kia</td><td style=";">Sportage</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">2015</td><td style=";">Diesel</td><td style=";">Sold</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Fiat</td><td style=";">500L</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">2014</td><td style=";">Petrol</td><td style=";">Not for Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Audi</td><td style=";">A3</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">2013</td><td style=";">Petrol</td><td style=";">Not for Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Audi</td><td style=";">A3</td><td style="text-align: right;;">2015</td><td style="text-align: right;;">2016</td><td style=";">Petrol</td><td style=";">Not for Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Kia</td><td style=";">Sportage</td><td style="text-align: right;;">2015</td><td style="text-align: right;;">2015</td><td style=";">Diesel</td><td style=";">For Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Ford</td><td style=";">Focus</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">2015</td><td style=";">Diesel</td><td style=";">Not for Sale</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Honda</td><td style=";">Accord</td><td style="text-align: right;;">2015</td><td style="text-align: right;;">2015</td><td style=";">Diesel</td><td style=";">Sold</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">{=IFERROR(<font color="#0000FF">INDEX(<font color="#FF0000">$A$1:$A$50,SMALL(<font color="#00FF00">IF(<font color="#800080">(<font color="#008080">$A$2:$A$50=$J$1</font>)*(<font color="#008080">$F$2:$F$50=$L$1</font>),ROW(<font color="#008080">$A$2:$A$50</font>)</font>),ROW(<font color="#800080">A1</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J3</th><td style="text-align:left">{=IFERROR(<font color="#0000FF">INDEX(<font color="#FF0000">$B$1:$B$50,SMALL(<font color="#00FF00">IF(<font color="#800080">(<font color="#008080">$A$2:$A$50=$J$1</font>)*(<font color="#008080">$F$2:$F$50=$L$1</font>),ROW(<font color="#008080">$A$2:$A$50</font>)</font>),ROW(<font color="#800080">B1</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K3</th><td style="text-align:left">{=IFERROR(<font color="#0000FF">INDEX(<font color="#FF0000">$E$1:$E$50,SMALL(<font color="#00FF00">IF(<font color="#800080">(<font color="#008080">$A$2:$A$50=$J$1</font>)*(<font color="#008080">$F$2:$F$50=$L$1</font>),ROW(<font color="#008080">$A$2:$A$50</font>)</font>),ROW(<font color="#800080">C1</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L3</th><td style="text-align:left">{=IFERROR(<font color="#0000FF">INDEX(<font color="#FF0000">$F$1:$F$50,SMALL(<font color="#00FF00">IF(<font color="#800080">(<font color="#008080">$A$2:$A$50=$J$1</font>)*(<font color="#008080">$F$2:$F$50=$L$1</font>),ROW(<font color="#008080">$A$2:$A$50</font>)</font>),ROW(<font color="#800080">D1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,118,523
Messages
5,572,639
Members
412,478
Latest member
MakeItWorkVBA
Top