Excel formula question - eliminating blanks?

Question

Ok, I'm working on a spreadsheet for my security department. On the spreadsheet, we enter our beginning mileage and ending mileage when we drive the company vehicles. If they need gas, we have to refuel them and document this on the spreadsheet. However, we only refuel them about once every two days. So my question is. . .

How do I put a formula in to create a list of ONLY the times we refuel the vehicles?

I have multiple sheets in 1 file. One of those sheets is ONLY for refueling information. I want to find a way for it to automatically update the different sheets when I enter the refuel information on the mileage sheet.

Any help would be appriciated!!!

Answer 1

You can use VLOOKUP to pull the data from the sheets with the beginning mileage, ending mileage etc. to the "refueling" sheet.



Does each vehicle have its own sheet? I'll assume that it does, for the sake of simplicity (if that's not the case, let us know).



In order to pull data from your first sheet to your second sheet (refueling sheet), you will need to find the specific trip. If you don't have a trip identifier, then maybe time and date will work.



Let's say the begining mileage, e.g., is in column B of Sheet 1. You'd have this formula on Sheet 2 (refueling sheet):



=VLOOKUP(A1, 'Sheet 1'!$A$1:$F$100, 2, FALSE)



- A1 is the trip identifier (again, could be time-date)

- 'Sheet 1'!$A$1:$F$100 is the range on your first sheet which has the general mileage data.

- 2 is a column offset

- FALSE means it will look for an exact match



This will take the trip identifier that is in A1 of Sheet 2, and look for it in column A of Sheet 1. When it finds it, it will go to the second column in the range (col. B of Sheet 1) and show what's there.



To get the value from col. C (ending mileage?), you'd change the 2 in the formula to a 3.



I hope this helps, but please add detail, or e-mail, if you need more info.

Best of luck.

.

Answer 2

It sounds like you need to create a pivot table. I'm not really good at pivot tables, so you'll need to check Excel's help files on them or ask someone that knows a little more about them.



Another option that might help you is to do an AutoFilter on the column where you enter the refueling data. Select all fo the cells on the worksheet then go to:



Data > Filter > AutoFilter



Afterwards, pick the arrow at top of the column for the drop-down list and pick Nonblanks from the list. That will list all of the non-blank rows.



The best way to do things would to be to put a pivot table on sheet 2 to summarize the data on sheet 1 as far as gas mileage for refills, total miles, etc.



Below is a help website from microsoft on pivot tables for Excel 2003.



http://office.microsoft.com/training/training.aspx?AssetID=RC010136191033

Answer 3

I am guessing the refueling has to do with the amount of gas used??

You can write the formula on the sheet and cell you want the answer to appear and use the other sheet/cells as the variables.

You need and if statement, probably nested.

Not sure what your sheets say, buts something like

(If B1 > galsUsed, sheet2 a5 ="Refueled",) You dont need an else cause if its not true you aren't doing anything. Unless you want a blank string then (If B1 > 0,sheet2 a5 gets answer, sheet a5 =" ")

Play with it on ONE sheet first.

Answer 4

This might work. Create a column that shows total miles.

Then have the main spreadsheet read something like this :

=if((TotalMiles>0),(TotalMiles ) , " ")

Take out the spaces that I had to put in to get YA to print the formula in it's entirety!)

If the part of this formula reads that if the value of that cell is less than 1, it should print a blank.

Alternatively, you could use the numeric value 0 instead of " ", which will print 0 instead of a blank.

Hope it works, hope I helped!

Answer 5

you are able to no longer do it by using formulation basically. you ought to use the "IF" difficulty on your formulation, yet which will only repeat the sparkling, no longer eliminate the line (row). Your basically way of doing so is to place in writing a macro (plug-in seen hassle-unfastened). i.e. for each each and each row in source column. If no longer sparkling: replica contents in dest column, improve row of dest column. else do no longer something next

Answer 6

Compress your data into one worksheet and apply a filter. (see data|filter)



Column labels:

date/time?

mi out

mi in

fuel gal.

fuel mi.



filtering on fuel gal. column for:

'blanks' will yield non-fuel data

'all' will yield all data

'non-blank' will yield fuel data