Maybe I need help with Lookups??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

garry05 - 06 Dec 2005 21:57 GMT

Hi all...a newbie here.

I'm okay with many Excel functions, but I don't go too deep: Can't d
macros, lookups, that sort of thing.

Here's my dilemma: Sheet-2 has a list of 10 aircraft, and the goal i
to accumulate the hours-flown for each aircraft. Sheet-2 Column A i
the aircraft# (1-10), B is the aircraft name, C is the accumulate
hours-flown. Keeping it simple, let's say that A1=1, B1=Cessna, an
C1=26.5

Now, back to Sheet-1: Someone flew the Cessna for 2 hours...in A1 I'l
enter 1 for the aircraft#, and in B1 I'll enter "2" for the hours-flown
Now Sheet-2 C1=28.5, or maybe some cell on Sheet-1, for all I care.

I can, of course, just go to Sheet 2, find the right column/cell an
update manually, but this is duplicated effort that leaves _way_ to
much room for human eror.

If anyone can help with this, it would be much appreciated...let m
know if you want a copy of the existing spreadsheet.

Thanks,
Garry (garry@lewisaire.com

--
garry05

Reply to this Message

Max - 07 Dec 2005 05:43 GMT

Here's one simple way to set it up to cumulate in Sheet2 using SUMIF

In Sheet1,

The aircraft #s would be listed in A1 down
The hours-flown in B1 down

So it'll be logged in cols A & B as, e.g.:
1 2
3 2
1 3
2 5
2 3
3 4
etc

(Aircraft numbers would be repeated down in col A)

In Sheet2,

Aircraft #s (unique) are listed in A1 down,
Aircraft names listed in B1 down,
Accumulated hours to be computed in C1 down

Put in C1: =SUMIF(Sheet1!A:A,A1,Sheet1!B:B)
Copy C1 down

Col C will return the required cumulative figures
for the corresponding aircraft #s from Sheet1

Should you have an "initial" log in Sheet2 which needs to be "integrated",
just transfer/include this log into Sheet1 by copying and pasting over
Sheet2's cols A & C into Sheet1's cols A & B. Then continue the "new"
logging of the aircraft #s & hours-flown in the lines below.

And for a cleaner look in Sheet2,
we could suppress the display of zeros via, in Sheet2:
Click Tools > Options > View tab > Uncheck "Zero Values" > OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> Hi all...a newbie here.
>
[quoted text clipped - 23 lines]
> --
> garry05

Reply to this Message

garry05 - 07 Dec 2005 12:53 GMT

Hi Max:
Thanks much for your help!

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.

Again, if you want to see the actual spreadsheet, I'm glad to provide
it.

Thanks,
Garry (lewisaire.com)

Max Wrote:

> Here's one simple way to set it up to cumulate in Sheet2 using SUMIF
>
[quoted text clipped - 46 lines]
> --
> garry05[/color]

Signature

garry05

Reply to this Message

Biff - 07 Dec 2005 22:05 GMT

Max,

I posted a similar reply (use Sumif) to the multi-post in .Misc.

I sent the OP an email and I'm awaiting a response.

Biff

> Hi Max:
> Thanks much for your help!
[quoted text clipped - 84 lines]
>> --
>> garry05[/color]

Reply to this Message

Max - 08 Dec 2005 02:26 GMT

Thanks for the alert, Biff !
(.. don't worry about the redundancy, I'm used to it <g>)

Better to close it here & have developments / discussions continued
in the OP's post(s) over in .Misc.

Garry: ugh <g>, you multi-posted, which you shouldn't have.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> Max,
>
[quoted text clipped - 3 lines]
>
> Biff

Reply to this Message