Noob - Infinite Auto-Fill Without Slowing Down the Worksheet

[Yearsmama]

I've searched the internet for a flight log that tracks flight hours the way Army pilots need them tracked and I don't think it exists. I've figured everything out the way I need it done, however, there are a lot of guys I work with that want this to work for them and they are no good in excel.
(USING EXCEL 2010) I have 5 columns (C:G) that are VLOOKUP from an aircraft selection column, and one column that is a simple sum formula. Some guys have thousands of flights, if they knew how to use excel they could enter their flight and autofill from the row above. I don't want to teach everybody how to do this, and I want to protect the sheet from them breaking formulas. If I click and drag the VLOOKUP columns down thousands of rows, the worksheet takes forever to calculate and it is filled with #N/A (which screws up the array formulas that it populates. Is there a way to make this more efficient and user friendly? Any ideas would be great.

 

[Yearsmama]

Understandable, I'd be more than glad to send it to you if you want to take that kind of time to review it. I didn't want to post my email on youtube so I created a gmail for the questions on the sheet. shoot me an email excelflightlog@gmail.com or private message me your email and I'll send it to you if you feel up to it.

Thanks,
Landon

 

[Norie]

Landon

I'll PM you my email and you can send the workbook if you want.

Though I'll admit that I'm no formula guru.

I was actually hoping that if you posted some examples of your formulas
here someone that is could offer some advice - perhaps even to say that
I'm wrong in thinking the array part is a problem.

I'll take a look though, I do know a couple of things.

 

[Yearsmama]

Gotcha, I can't just look at a formula on these blogs, but being that you can here is an example:

{=SUM(IF(('Flight Entry'!$B:$B=$C$15)*('Flight Entry'!M:M="Y")*('Flight Entry'!$AQ:$AQ<'Hours Breakdown'!$E$21),'Flight Entry'!$Z:$Z))}

This formula is to check how many hours of Pilot in Command(PIC) time you have in a specified aircraft within the last ____ days.

('Flight Entry'!$B:$B) is the column with the selected aircraft for that day's fight.
($C$15) has the data validation list of aircraft I've flown.
('Flight Entry'!M:M) is the column that you check Y or N for PIC time.
('Flight Entry'!$AQ:$AQ) is a helper column to show how many days it's been since that flight.
('Hours Breakdown'!$E$21) is where you enter a specified number of days.
('Flight Entry'!$Z:$Z) flight hours logged that period.

There is approximately 75 of these formulas on the flight time summary tab, plus about 60 SUMIF formulas, and 30 SUM formulas. It's probably the reason for a slow sheet. I just can't think of a better way to do it. It only takes about 10 seconds to calculate the sheet, but autocalc is out of the question. I don't know how to insert an image on this forum yet so I can't include it, but there is a good shot of it on the youtube video I posted.

Thanks again,
Landon

 

[Norie]

Landon

I've had a quick look at the workbook and some of the formulas in it, not found te one you've posted yet though.

Anyway on the Hours Breakdown worksheet you seem to have formulas that don't need to be array entered.

For example in A4:

{=SUMIF('Flight Entry'!E:E,"Y",'Flight Entry'!$Z:$Z)}

That doesn't need to be an array formula, and if you enter it normally with just enter instead of CTRL+ENTER you get the same answer.

Further down in J26 you have this:

={SUMIF('Flight Entry'!$H:$H,'Hours Breakdown'!G26:I26,'Flight Entry'!$Z:$Z)}

At first I thought this might need to be an array formula because of the multiple cells for the 2nd argument.

However when I looked further I noticed G26:I26 were merged.

I changed that argument to just G26 and again entered it normally, the result was the same as the original array formula.

Finally found the formula you posted, or a very similar one anyway, in A9.

I tried this and got the same result:

=SUMPRODUCT(--('Flight Entry'!C2:C126=C8),--('Flight Entry'!E2:E126="Y"),'Flight Entry'!Z2:Z126)

Well, that's what I've found so far - no idea if changing them will make any difference obviously I've not checked all the formulas.

 

[Yearsmama]

Thanks for the review....I've never even heard of SUMPRODUCT, there is still a ton of this program I have no clue about. Do you think SUMPRODUCT would speed be more efficient? The others make sense to me though. Thanks again.

 

[Norie]

I'm really not the person to ask about speed/efficiency of formulas.

If you search the board then youll probably find more information/debate about that.