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.
(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.
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
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.
(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.
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