Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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...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 error. 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 ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maybe I need help with "Lookups"?
Hi!
Assume on sheet2 you have: A1 = Acft # B1 = Name C1 = Hours A2:A11 = 1,2,3,4...10 In sheet2 C2 enter this formula and copy down to C11: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) This assumes that every time an acft flies you make a new entry on sheet1 in a new row. Biff "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...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 error. 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
|
|||
|
|||
Hi Biff!
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 ) Quote:
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Maybe I need help with "Lookups"?
Sent an email.
Biff "garry05" wrote in message ... Hi Biff! 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 ) Biff Wrote: Hi! Assume on sheet2 you have: A1 = Acft # B1 = Name C1 = Hours A2:A11 = 1,2,3,4...10 In sheet2 C2 enter this formula and copy down to C11: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) This assumes that every time an acft flies you make a new entry on sheet1 in a new row. Biff -- garry05 -- garry05[/color] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|