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.

 

[GlennUK]

If your formula is returning #N/A then you aren't testing for blanks to prevent calculations .... like;

=IF(A1="","",VLOOKUP(blah blah etc etc ))

that will prevent your unnecessary #N/A messages, and will speed up calculation, and the VLOOKUPs will only calculate for filled cells. Of course, it will slow down as rows are filled in ... you may have to convert areas to values after a period of time ( and protect them and precedent cells ), to speed processing back up again.

 

[Yearsmama]

Thanks brother, I'll go with that and just make an extra worksheet with instructions. There aren't many pilots with over 2,000 flights anyways, so it won't slow down for most guys.

 

[Yearsmama]

And I did just learn how to make the VLOOKUP return a blank rather than an error prior to entering source data....like I stated, I'm a noob. My array formulas all work now. I appreciate the help.

 

[GlennUK]

I'm glad that you've got it working OK now.

 

[Norie]

What array formulas? If you are also copying them down it might be those that are causing the slow down.

 

[Yearsmama]

I'm not copying those down. They are on another tab that makes it so you can search your hours by different criteria. For example, the unit I'm heading to hase to fly 6 hours under night vision goggles every 45 days to stay current. Some units have different requirements, so I had to make array formulas that work off data validation lists and time frames so you can search an airframe currency by date and mode of flight. It is a slow sheet (10 or so seconds to load) but I turned off autocalculate and I don't need to check currency very often. Thanks for all the input though guys. I'm new to excel's advanced functions so I'm sure I'll be reading the forum and posting a lot. -Landon

 

[Norie]

Landon

Even if you aren't copying them down in might be worth taking a look at those array formulas.

As far as I'm aware some types of array formula can significantly slow calculation down.

If they are being used to search for something based on criteria it might be possible to use different formulas.

 

[Yearsmama]

Norie,
I'm sure there are more efficient ways of doing what I did. I just read through different functions of excel 2010 and made my own assumption of what would be the best way to tackle my idea. I uploaded the function of my spreadsheet to youtube to see if anybody outside my unit would want it. here is the link:

http://youtu.be/qPs5zafbpTY

Pretty much the entire last page that I explain is array formulas. If you think there is a better way to accomplish the task that I've set out to do, please let me know. It works now, but there is always room for improvement; especially for those of us just start to learn excel on our own.

Thanks for you help,
Landon

 

[Norie]

Landon

It's hard to tell without actually seeing any formulas.