Maybe I need help with Lookups??

Hi all...a newbie here.

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

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

Now, back to Sheet-1: Someone flew the Cessna for 2 hours...in A1 I'll
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 and
update manually, but this is duplicated effort that leaves _way_ too
much room for human eror.

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

Thanks,
Garry (garry@lewisaire.com)


--
garry05

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
--
"garry05" <garry05.1znhhn@news.officefrustration.com> wrote in message
news:garry05.1znhhn@news.officefrustration.com...
>
> Hi all...a newbie here.
>
> I'm okay with many Excel functions, but I don't go too deep: Can't do
> macros, lookups, that sort of thing.
>
> Here's my dilemma: Sheet-2 has a list of 10 aircraft, and the goal is
> to accumulate the hours-flown for each aircraft. Sheet-2 Column A is
> the aircraft# (1-10), B is the aircraft name, C is the accumulated
> hours-flown. Keeping it simple, let's say that A1=1, B1=Cessna, and
> C1=26.5
>
> Now, back to Sheet-1: Someone flew the Cessna for 2 hours...in A1 I'll
> 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 and
> update manually, but this is duplicated effort that leaves _way_ too
> much room for human eror.
>
> If anyone can help with this, it would be much appreciated...let me
> know if you want a copy of the existing spreadsheet.
>
> Thanks,
> Garry (garry@lewisaire.com)
>
>
> --
> garry05

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
>
> 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
> --
> --
> garry05[/color]


--
garry05

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

"garry05" <garry05.1zoetn@news.officefrustration.com> wrote in message
news:garry05.1zoetn@news.officefrustration.com...
>
> 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
>>
>> 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
>> --
>> --
>> garry05
>
>
> --
> garry05[/color]

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
--
"Biff" <biffinpitt@comcast.net> wrote in message
news:uIy4gp3#FHA.1600@TK2MSFTNGP11.phx.gbl...
> 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