Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ) -- garry05 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maybe I need help with Lookups??
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" wrote in message ... 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 ) -- garry05 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maybe I need help with Lookups??
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maybe I need help with Lookups??
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" wrote in message ... 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] |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maybe I need help with Lookups??
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converging two lookups? (would save me AEONS of time!) | Excel Worksheet Functions | |||
Q: Using lookups to normalize a database? | Excel Discussion (Misc queries) | |||
External lookups won't load | Excel Discussion (Misc queries) | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Lookups | Excel Discussion (Misc queries) |