lookup data from another worksheet

cfoster

New Member
Joined
Feb 23, 2012
Messages
3
Greetings,

I have a workbook containing various vehicle data. On one of these sheets (called Report) I show some summaries and visual representations of the data contained in the workbook, for printing purposes. Each sheet in the workbook contains vehicle registration numbers in one of the columns.

So in the Report sheet, in column P, I wish to display a number which corresponds to last months fuel costs, which I'd like to be obtained from a sheet called Fuel.

6778615300_08bbaf3991.jpg


(currently the number is just copy+pasted, this is too tedious of a process to be continued however)

In the Fuel sheet, starting from column F, dollar amounts are entered for each registration number for the month.

6924733219_23e11ea9c2.jpg


So the row in the Fuel sheet will vary (it contains vehicles which are not on the Report sheet) but to get the right one I'd like to use the vehicle registration number obtained from the Report sheet in column B. The registration numbers in the Fuel sheet are in column D. The column to obtain the figure from would be the last column in the corresponding row which contains data.

The ordering of the vehicles (the rows) on the Report sheet may vary but the rows on the other sheets will likely just remain in their current order - note that the rows will not correspond to each other.

I can use LOOKUP to obtain the number, but it's not very dynamic and if I move the rows around things tend to get muddled. Hence why I'd like it to use the registration numbers to be more elegant.
I am pretty sure I need to use VLOOKUP in some way, I suspect I need to nest some formulas to get this working perhaps? I would appreciate some guidance in this regard. I am using Excel 2010. Also I should mention I don't mind if the solution is via VBA or other macro.


Thank you,
Charles
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Welcome to the Board!

Unfortunately, I can't read your images all that well. Take a look at the link in my sig, which will let you post shots of your sheets that others can copy right into Excel to get you the right formula.
 
Upvote 0
Thanks for the welcome! I've often used this forum as a resource and hopefully I'll be able to contribute to it in the future as well.

I have installed the Add-In and it appears to be working alright... let's give it a try...


Here is the a couple lines from the "Report" sheet as mentioned above:

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; 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><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH><TH>Q</TH><TH>R</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Current Lease</TD><TD style="BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Monthly</TD><TD style="BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Total </TD><TD style="BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Months</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Odometer</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; BORDER-RIGHT: black 1px solid">Reading</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Last Month</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">6 month</TD><TD style="BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; FONT-WEIGHT: bold">Projected</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff"></TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Registration</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Make</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Model</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Description</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; BORDER-RIGHT: black 1px solid">Driver or Site</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Commenced</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Finish</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Term</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; BORDER-RIGHT: black 1px solid">Kms</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Rental</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">over Lease</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">to End</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Reading</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; BORDER-RIGHT: black 1px solid">Date</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Fuel Cost1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff">Trend</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #8db4e2; COLOR: #ffffff; FONT-WEIGHT: bold">Final kms2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid">1</TD><TD style="BACKGROUND-COLOR: #b8cce4; BORDER-TOP: black 1px solid">SblahK</TD><TD style="BACKGROUND-COLOR: #b8cce4; BORDER-TOP: black 1px solid">Nissan</TD><TD style="BACKGROUND-COLOR: #b8cce4; BORDER-TOP: black 1px solid">Xtrail</TD><TD style="BACKGROUND-COLOR: #b8cce4; BORDER-TOP: black 1px solid">wagon</TD><TD style="BACKGROUND-COLOR: #b8cce4; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Someone</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">3/12/2010</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">2/12/2012</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">24</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">80000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">$ 1,582.26 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">$ 37,974.24 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid">46142</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">30/12/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid">$ 448.20 </TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid">85928</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid">2</TD><TD style="BACKGROUND-COLOR: #b8cce4">SblahM</TD><TD style="BACKGROUND-COLOR: #b8cce4">Hyundai</TD><TD style="BACKGROUND-COLOR: #b8cce4">ix35</TD><TD style="BACKGROUND-COLOR: #b8cce4">wagon</TD><TD style="BACKGROUND-COLOR: #b8cce4; BORDER-RIGHT: black 1px solid">SomeoneElse</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">15/09/2011</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">14/09/2013</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">24</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">60000</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">$ 801.78 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid">$ 19,242.72 </TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #d9d9d9; COLOR: #595959; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">19</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid">9345</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid">30/12/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid">$ 438.58 </TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid">64358</TD></TR></TBODY></TABLE>
Report




And here are some lines from the Fuel sheet:

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; 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><TH>M</TH><TH>N</TH><TH>O</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: right"></TD><TD>Registration</TD><TD>Card No.</TD><TD>Full #</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">july</TD><TD style="TEXT-ALIGN: center">august</TD><TD style="TEXT-ALIGN: center">september</TD><TD style="TEXT-ALIGN: center">october</TD><TD style="TEXT-ALIGN: center">november</TD><TD style="TEXT-ALIGN: center; BORDER-RIGHT: black 1px solid">december</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid">january</TD><TD style="TEXT-ALIGN: center">february</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid">$ 4,501.81 </TD><TD style="TEXT-ALIGN: right">1</TD><TD>SblahK</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">60012345</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><TD style="TEXT-ALIGN: right">$ - </TD><TD style="TEXT-ALIGN: right">$ 258.81 </TD><TD style="TEXT-ALIGN: right">$ 432.52 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid">$ 3,017.44 </TD><TD style="TEXT-ALIGN: right">2</TD><TD>SblahM</TD><TD style="TEXT-ALIGN: right">54321</TD><TD style="TEXT-ALIGN: right">60054321</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">$ 206.86 </TD><TD style="TEXT-ALIGN: right">$ 17.84 </TD><TD style="TEXT-ALIGN: right">$ 220.70 </TD><TD style="TEXT-ALIGN: right">$ 170.11 </TD><TD style="TEXT-ALIGN: right">$ 165.37 </TD></TR></TBODY></TABLE>
Fuel

I hope that makes it clearer. In the Fuel sheet there is currently data out to column Z and next month column AA will be filled in.


Thank you,
Charles
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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