Originally Posted by
bbarrene
Could you please explain to me as to what you mean by "volatile by association". Also, by "relatively expensive formulae", do you mean that these formulas require a lot more memory?
For more info. on Volatility see the link in my signature.
Arrays (along with SUMPRODUCT) are more "expensive" than non-array formulae given the way the functions are processed.
It follows that if said Arrays are Volatile then they are potentially calculating more often than is necessary and in so doing using more resources than would otherwise be deemed ideal.
Avoiding Volatile Arrays is generally a good idea for this reason - wherever & whenever possible.
Originally Posted by
bbarrene
Do you mean I should change the formulas to include OFFSET rather than INDEX or should I just delete the Names altogether. My guess was that I should just change the formula. Could you please verify?
I meant that you should revise all of your "data" names to use INDEX rather than OFFSET thereby removing the Volatility from the Array - you appear to have done this.
Originally Posted by
bbarrene
First, I wanted to hide the #NUM! cells that appear if the search results do not reach that row.
IMO you would be best served conducting a one off calculation to determine the number of search results that are to be generated based on selections.
The reason behind this is that you can reduce the number of times you process the Array - ie only process when necessary.
The below would tell you how many records are to be returned:
You can then use this value to prevent unnecessary calculations with the Array and in turn handle the #NUM errors appropriately, eg:
Using your sample file where given existing search terms the SUMPRODUCT in A4 will generate a result of 2 we're now calculating the Array only twice (rows 6 & 7) + 1 SUMPRODUCT rather than calculating the Array 220 times as was previously the case.
In the above we're returning a NULL ("") where there is no search result - this will still not handle the errors in the INDEX results in the remaining columns... to cater for those we must adjust each formula based on the value in Col A, eg:
With the above IF construct being repeated for each column in results table.
Originally Posted by
bbarrene
Second, in the column titled "Image", there will be a hyperlink to a PDF file that will contain all of the information in that row as well as a picture of the part. I thought the hyperlink would be accessible to click on in the SEARCH worksheet but it is not. Would it be possible to make it so that a person could click on the hyperlink in the SEARCH worksheet rather than having to change to the LIST worksheet to open the PDF file. If it is, could either of you help me with the coding?
Without seeing the links it's hard to give any specific advice - depending on the text returned by the INDEX you may find you can encase within a HYPERLINK function to generate the link - eg:
If you need further assistance on this post back with an updated version in respect of all of the above.
Bookmarks