Comments for Excel and UDF Performance Stuff https://fastexcel.wordpress.com
Charles Williams on 'Making Excel go Faster'
Wed, 20 Feb 2019 11:54:36 +0000
hourly
1 http://wordpress.com/
Comment on Excel Range.Value(ValueType): What is this parameter? by Nathan https://fastexcel.wordpress.com/2017/03/13/excel-range-valuevaluetype-what-is-this-parameter/#comment-6277
Wed, 20 Feb 2019 11:54:36 +0000http://fastexcel.wordpress.com/?p=2059#comment-6277I have used this in the past when dealing with precision over 15 dp, it helped.
]]>
Comment on VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP by VS Rawat https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/#comment-6219
Fri, 08 Feb 2019 12:22:13 +0000http://fastexcel.wordpress.com/?p=359#comment-6219Good one. I had to think it over to comprehend what happens when partial match is found, but exact match may be there later down in the table. But no, if entire PartNumber matches, then it has to be an exact match even with an approximate search, even though PartNumber1, PartNumber2, etc. further variations of PartNumber exist down below. Insightful. Thanks.
]]>
Comment on INDIRECT – Excel’s Most Evil Function by jeffrey Weir https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/#comment-5843
Sun, 20 Jan 2019 20:48:06 +0000http://fastexcel.wordpress.com/?p=1311#comment-5843You can always just name your sheet tabs 1, 2, …, 40. I’ve used this trick to make someone elses’ workbook usable, and it was certainly worth it.
Alternatively spend your efforts optimizing the calculation chains downstream , to see if you can’t significantly speed them up to the point that the volatility introduced by the existing INDIRECT is no longer noticeable.
]]>
Comment on INDIRECT – Excel’s Most Evil Function by Jim Dorbish https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/#comment-5824
Wed, 16 Jan 2019 16:21:06 +0000http://fastexcel.wordpress.com/?p=1311#comment-5824Oh, so I have to write out each tab name into the Choose formula? I thought that was the purpose of listing out all the tab names in the Table?
]]>
Comment on VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP by fastexcel https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/#comment-5823
Wed, 16 Jan 2019 16:18:28 +0000http://fastexcel.wordpress.com/?p=359#comment-5823For VLOOKUP the data needs to be sorted ascending and it does not care how many characters the cell has. You can of course use a double INDEX-MATCH combination instead of VLOOKUP and the you can specify in the MATCH whether the data is sorted ascending or descending.
]]>
Comment on VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP by middlefastexcel https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/#comment-5822
Wed, 16 Jan 2019 16:02:09 +0000http://fastexcel.wordpress.com/?p=359#comment-5822Hello fastexcel & community,

I found your blog and I’m really happy for all the Info, thanks a lot!
I have a question concerning that topic and would be really happy for an answer.

So that the formula works the table must be sorted. If ascending or descending doesn’t care? And does it play a role if the looked up Column is a mix between numbers and characters how many characters the cell has? For example I want to look up:
exammpleword.6584587.48754585477-554774458-66658-5547854

Thanks a lot

]]>
Comment on INDIRECT – Excel’s Most Evil Function by fastexcel https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/#comment-5821
Wed, 16 Jan 2019 14:44:42 +0000http://fastexcel.wordpress.com/?p=1311#comment-5821Well, its doable but your CHOOSE formulas will get rather long!
]]>
Comment on INDIRECT – Excel’s Most Evil Function by Jim Dorbish https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/#comment-5818
Tue, 15 Jan 2019 22:24:54 +0000http://fastexcel.wordpress.com/?p=1311#comment-5818Tab names would be around 40. Would you consider that to be too many?
]]>
Comment on INDIRECT – Excel’s Most Evil Function by fastexcel https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/#comment-5817
Tue, 15 Jan 2019 22:22:27 +0000http://fastexcel.wordpress.com/?p=1311#comment-5817Depends how many sheets you have to choose from. With CHOOSE you have to be able to set up the choices in advance (using Named Ranges makes it easier to handle) and you are limited to a reasonable number of choices. INDIRECT is more flexible but has many more downsides.
]]>
Comment on INDIRECT – Excel’s Most Evil Function by Jim Dorbish https://fastexcel.wordpress.com/2016/04/25/indirect-excels-most-evil-function/#comment-5816
Tue, 15 Jan 2019 22:15:41 +0000http://fastexcel.wordpress.com/?p=1311#comment-5816I’m using Indirect to reference a cell value that contains the name of a sheet in the same workbook. Am I able to use choose instead of indirect to refer to a cell containing the name of a sheet name?
]]>