Apr 16 2019 09:37 AM
I have a worksheet that is doing a ton of calculations so I am trying to streamline it a bit so it will be more friendly to slower computers, Originally I set up dynamic named ranges using offset OFFSET('FLOP DATA'!$A$13,0,0,COUNTA('FLOP DATA'!$A:$A),1))
This works fine and returns the accurate result when doing a count of 74
I am wanting to switch these to a less volatile argument so I was using
('FLOP DATA'!$A$13:INDEX('FLOP DATA'!$A:$A, COUNTA('FLOP DATA'!$A:$A)))
This is returning a count value of 63 and dropping of the bottom of the range, The values in the range are all numbers and have no blanks etc...
Also is using IFERROR(INDEX(FLOP,MATCH($P14,DBNUM,0)),"") less of a drain on computer resources than using vlookup?
Apr 16 2019 09:55 AM
@Dichotomy66 , perhaps it shall be
('FLOP DATA'!$A$13:INDEX('FLOP DATA'!$A:$A, COUNTA('FLOP DATA'!$A:$A)+13))
or so, index starts from first row and you need to add some offset.
In general with dynamic ranges OFFSET is more easy in maintenance (at least for me), but INDEX is much better from performance point of view. Thus on small ranges it doesn't matter what to use, but on relatively big ones INDEX is definitely more preferable.
Apr 16 2019 10:15 AM
That does seem to work I will have tp test is a lot.On this worksheet the data set is replaceable and can be anywhere from a 30x 8 array to an 1177 x 8 array . Also I am using about 14 named ranges so this is especially where I am trying to get rid of offset though it is super easy to use
Apr 16 2019 10:50 PM