Computers & Electronics

Help with an Excel question

  • Last Updated:
  • May 25th, 2010 8:15 pm
Tags:
None
Deal Guru
User avatar
Dec 3, 2003
14494 posts
252 upvotes
Mississauga

Help with an Excel question

I have a spreadsheet with 2 worksheets for rental of equipment, by the mile.
The equipment is not located at the office so we need to have the miles IN and OUT given to us by the renter.

Worksheet A has a pull-down list referencing the list of equipment in Column A on Worksheet B.

When the user selects a specific list of equipment on Worksheet A, the user will enter the miles OUT (starting miles) of the unit.

When the Miles IN is entered upon return, I would like that number to go to
Worksheet B, in column B,

That way, when a unit is rented again, I can see what the hours were when it was last rented, to ensure that the renter is giving me the correct Hours OUT.

Confused?
Heatware 47-0

"Giving money to government is like giving whiskey and car keys to teenage boys."
4 replies
Sr. Member
User avatar
May 19, 2008
757 posts
16 upvotes
do you want the number of miles as in miles in - miles out? ex out 50km, in 150 so you want 100km? or just to copy both lines to the same location? or total miles only as vehicle has only 150km?

if as i undersaand u just want to copu 1 cell from sheet A to sheet B which is miles in?

maybe all you need is =Sheet1!B2 this will pull data from sheet 1 cell b2, to anyother sheet in this workbook, you can adjust the sheet, or lines, and you can create formulas using this like sums and such
Deal Guru
User avatar
Dec 3, 2003
14494 posts
252 upvotes
Mississauga
devils-haven wrote: do you want the number of miles as in miles in - miles out? ex out 50km, in 150 so you want 100km? or just to copy both lines to the same location? or total miles only as vehicle has only 150km?

if as i undersaand u just want to copu 1 cell from sheet A to sheet B which is miles in?

maybe all you need is =Sheet1!B2 this will pull data from sheet 1 cell b2, to anyother sheet in this workbook, you can adjust the sheet, or lines, and you can create formulas using this like sums and such
Each time a Unit is used, the miles are increased.
I want to be able to show the most recent miles IN for a designated unit.
For example...

If Unit 123 was used 5 days ago and the IN miles were 500
If it was used again 2 days ago and the IN miles were 540,
I would like to be able to easily show those 540 miles when looking at the unit.

FYI: I have 50 units to rent, so you can see it would be very difficult to scroll through all the transactions to find the most current mileage.
Heatware 47-0

"Giving money to government is like giving whiskey and car keys to teenage boys."
Deal Fanatic
User avatar
May 11, 2008
9545 posts
960 upvotes
Can`t you create a pivot table of all the data and just summarize it by type of rental and the max miles in the OUT field?

Ie you have 3 columns
Type - IN - OUT in A - C
Create a pivot table and select columns A to C as your data set.
Put Type in the left most box ("Drop Row Fields Here"). Then put the OUT in the center portion ("Drop Data Items Here").
It defaults to summing the values. But right click the box that says Sum of OUT and click on Field Settings. Choose Max and it should give you the latest miles (assuming the last one is the largest value - which it should be if it is correct)
Deal Guru
User avatar
Dec 3, 2003
14494 posts
252 upvotes
Mississauga
rems wrote: Can`t you create a pivot table of all the data and just summarize it by type of rental and the max miles in the OUT field?

Ie you have 3 columns
Type - IN - OUT in A - C
Create a pivot table and select columns A to C as your data set.
Put Type in the left most box ("Drop Row Fields Here"). Then put the OUT in the center portion ("Drop Data Items Here").
It defaults to summing the values. But right click the box that says Sum of OUT and click on Field Settings. Choose Max and it should give you the latest miles (assuming the last one is the largest value - which it should be if it is correct)
Thanks

I will try that.
Heatware 47-0

"Giving money to government is like giving whiskey and car keys to teenage boys."

Top

Thread Information

There is currently 1 user viewing this thread. (0 members and 1 guest)