Recently I came across a brilliantly engineered FAI form 3 (per as9102) on the Cove. The attachment as I recall was from Cari Spears but in doing a search….unable to locate it a second time. This form is in excel format, the “results” column changes from black and red font depending if the number is in or out of the range shown in the “tolerance” column.

To me, the form seems to be dependant upon another excel file that I do not have. I say this because numerals and characters can be updated in any box but no formula associated to it so I feel the form is linked to a protected database.

I appreciate any guidance towards unlocking this challenge.

I have the MS excel inside/out book (if this helps).

MQA

Recently I came across a brilliantly engineered FAI form 3 (per as9102) on the Cove. The attachment as I recall was from Cari Spears but in doing a search….unable to locate it a second time. This form is in excel format, the “results” column changes from black and red font depending if the number is in or out of the range shown in the “tolerance” column.

To me, the form seems to be dependant upon another excel file that I do not have. I say this because numerals and characters can be updated in any box but no formula associated to it so I feel the form is linked to a protected database.

I appreciate any guidance towards unlocking this challenge.

I have the MS excel inside/out book (if this helps).

MQA

Try here (http://elsmar.com/Forums/showthread.php?p=187672#poststop), or perhaps here (http://elsmar.com/Forums/showthread.php?p=223931#poststop). I found these by clicking on Post Attachments List at the top of the page, and searching for FAI.

Moxie - the FAI forms are very nice, but, just a heads up / word of caution.
After digging into the methodology a bit, just be careful - it appears that conditional formatting is being used in conjunction with being cell-specific to the tolerance. In other words, you can copy the formula down, but, you will have to adjust the formula every time based on the tolerance that you enter because the formula is not referencing the tolerance from another cell.
As I said, unique ideas are awesome and inspire others, so, I don't want you or the author of the forms to get the idea that I am just ripping these, because I am not. But, personally, with all of the work that you will have to do anyway (I think), you would probably be better off rolling the dice and telling your inspectors to highlight out of tolerance conditions with a red font than to depend on them to change / conditional format every time.
Maybe a Macro would work better for what you are looking for?

Thanks Jim, It's the attachment posted by Bogie.

Per my original post, can a Excel guru give me more insight?

As per the other response this spreadsheet is using conditional formatting. Select the cell you're interested in altering, select format, select conditional formatting and you can delete/add/edit the format.

It is odd that the tolerances are added directly into the conditional format, I can only assume the writer didn't use the cell box because of the +/- indicators, you could, however, move these to their own cell, and just reference the tolerance number by cell, this way you could edit the tolerance values anytime without having to change the conditional formatting as well.

Got it, something like this.

Yup, that way you can just plug in your values and tolerances and away you go! ;) (song and fast moving vehicle completely off topic)

dwDa5dMmfZ4

There is a problem though if your tolerances are +.005 / - .010. The inspector would either have to change the nominal from whats on the print or monkey with the spreadsheet.

MoxieQA,

I altered your spreadsheet to accommodate what I think you are looking for. You can cut and paste the results field and the conditional formatting will stay the same.

Hope this helps......:bigwave:

Thank you Frank for the form udpate. I'm please with the final result.

MQA