Help with "Lookup"

[garry05]

Hi all...a newbie here looking for some help.

First of all, understand that I'm sorta okay with many Excel functions,
but I don't go too deep: Can't do macros, lookups, etc.

Here's my dilemma: Sheet-2 has an inventory of 10 aircraft ...Column-A
shows the aircraft# (1 thru 10), column-B shows the the associated
aircraft name, Column-C shows accumulated hours-flown.
Keeping it simple, A1=1 B1=Cessna C1=26

Now, back to Sheet-1: Someone flew the Cessna for 2 hours, and I need
to ADD that time to C1 of Sheet-2, from (let's say) Sheet-1 (A1) and
(A2). So the goal is to collect and accumulate aircraft time based on
whatever inventory number is entered into (A1) with the hours that are
entered into (A2).

If anyone can help with this, let me know and I'll email you the
existing .xls file so you can see what it's all about.

Thanks,
Garry ([email protected])

 

[Earl Kiosterud]

Garry,

You don't give the specifics of Sheet 1 (which could use a more descriptive
name, like Flights). I'll presume it looks like this:

Aircraft FlightTime
1 2
2 3
1 1

(And sheet-2 could use a more descriptive name, like Aircraft):

Aircraft Model Total hours
1 Cessna
2 Beechcraft
3 Cessna

C2 in this sheet might look like:

=SUMIF(Flights!$A$2:$A$10, A2, Flights!$B$2:$B$10)

Copy down with the fill handle. I kept row 1 for column headings.

Earl Kiosterud
www.smokeylake.com

 

[garry05]

Earl:

Thanks very much for your help! It worked like a dream! Man, that
thing has been driving me nuts!

Well, there's not much chance I can ever repay your help with my vast
Excel knowledge, but if you ever wanna join the fun on LewisAire
Virtual Airlines (MS Flight Simulator), let me know and you've got a
lifetime membership; I own it.

Take care, and thanks again. With kind respect, I'll keep your contact
info handy; chances are pretty good that I might bug you again one day.


Garry

 

[garry05]

Hi Earl:

Okay, the SUMIF works well, but it won't work for my application...I've
made a big mess of things by not asking the right questions, and by
trying to shortcut the explanation of what I really need. Let's go into
it backwards this time: First off, the Trip Report submitted by a
member (pilot) contains the flight#, hours-flown and aircraft info that
looks something like this: *5 Merlin III 425/41* (5) Is the aircraft
inventory#, (425) is the billing-rate per hour for that plane, and (41)
is the fuel-burn per hour for that plane.

We have a number of members (pilots) and I need to keep a record of
flights each pilot has flown, okay? So there are separate sheets for
(let's say) Bob, Sue, Jim, Fred, and so on, and this grows as we add
new pilots. When a Trip Report comes in from Sue, I go to her sheet
and enter the Flight#, hours-flown, billing-rate, and fuel-burn (each
flight she makes is a new row)...from that I do all the other math for
revenue, fuel-cost, and other summaries. This all works okay, but
what's missing is the accumulation of aircraft-time, no matter who
flies it or how often they fly it.

Obviously I could do what I'm doing now (go to their log sheet and
enter the data), then go to a separate sheet and log the aircraft# and
flight-time again, but this is duplicated efforts which invites entry
error.

The ultimate goals: (1) Reduce the number of steps required to enter
Trip Report data, and (2) Accumulate aircraft-time regardless of who
flies it or how or how often they fly it.

I'm attaching a cutout of a pilot log sheet (where I now enter Trip
Report info), maybe that'll help make the picture a little clearer.
Again, if you want to see the actual spreadsheet, I'm glad to provide
it.

Thanks,
Garry


+-------------------------------------------------------------------+
|Filename: LewisAire Spreadsheet.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4105 |
+-------------------------------------------------------------------+

 

[Earl Kiosterud]

Garry,

You're absolutely right about doing redundant data entry. It's more
error-prone, and the workbook should be working for you, not you for it.

One possibility is to have totals by aircraft on each sheet, in defined
locations. They can be hidden. You'd use the SUMIF construction we talked
about. If there aren't too may aircraft, this would be manageable. Then
the aircraft table (sheet) can easily sum those with across-sheet (sometimes
called 3D, though they wouldn't be in this situation). But...

The real solution is this: Consolidate the flight (pilot) sheets. Put all
the flight information in one big table. Yup. It'd look just like the
current sheets, with the addition of a Pilot column. Check out "Data across
multiple sheets" at http://www.smokeylake.com/excel/excel_truths.htm. This
is exactly your situation. There's an example workbook you can download and
look at. You may be inclined to reject the notion of not having a sheet for
each pilot, but I urge you to consider this carefully -- it will allow you
to work with your data in many ways that will not be possible without a lot
of rigamorole (writing macros, etc.) otherwise. It's kind of "now or later"
almost certainly.

Earl Kiosterud
www.smokeylake.com