Things that bother me about excel, and other pet peeves

[RichardS]

Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.

My 2 bobs worth.

Richard

 

[Lewiy]

Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.

Richard, personally I don't think that this is a bad thing. How would someone determine which functions would automatically use absolute ranges and which would not. And for the user, this would be inconsistant and possibly frustrating as absolute references are not always appropriate for such functions. I think having to remove the $ would be more irritating than having to put them in.

 

[mortgageman]

Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.

Richard, personally I don't think that this is a bad thing. How would someone determine which functions would automatically use absolute ranges and which would not. And for the user, this would be inconsistant and possibly frustrating as absolute references are not always appropriate for such functions. I think having to remove the $ would be more irritating than having to put them in.

How someone would determine which functions (and which function arguments) default to absolute would depend on usage. Richard's point - which I certainly agree with - is that in a v (or h) lookup, the lookup table will not change. Thus by intended usage, the first argument should default to absolute. Removing the $ would never be irritating in that case, since no one would ever remove it.

 

[Lewiy]

Dunno about a peeve, but I would estimated 99% of the times that I have used a VLOOKUP or SUMIF formula, the formula will be copied down a number of rows. Naturally, the table_array, or the range and sum_range, depending on the function, is a constant. If these ranges are in the same file, I have to manually change the references to absolute. I would have thought having absolute ranges for these types of formulae as the default would make sense. Probably applies to a number of other functions as well. I know I can name the range, but if it's just a working file which won't be used again, that's just one more step, no faster than changing the formula to absolute.

Richard, personally I don't think that this is a bad thing. How would someone determine which functions would automatically use absolute ranges and which would not. And for the user, this would be inconsistant and possibly frustrating as absolute references are not always appropriate for such functions. I think having to remove the $ would be more irritating than having to put them in.

How someone would determine which functions (and which function arguments) default to absolute would depend on usage. Richard's point - which I certainly agree with - is that in a v (or h) lookup, the lookup table will not change. Thus by intended usage, the first argument should default to absolute. Removing the $ would never be irritating in that case, since no one would ever remove it.

I often use VLOOKUP to determine whether, say the value in column A exists anywhere further down the list (ignoring cells above) for this purpose, I don't want the absolute values in there. I appreciate that often this is not the case with most people but it would create an inconsistancy in the way Excel interprets what you enter.

When you miss a bracket off the end of a formula Excel says "I think you've made an error here, do you want to use this amended suggestion". Perhaps if when you used VLOOKUP, it could say "I think you might want to use absolute values in your lookup range". That way, whether you want them or not, you don't have to go back and change all the ranges, just click Yes or No.

 

[klb]

Richard,
When you are typing in your lookup formula, just remember to hit F4 after you have entered your lookup range and your reference will change to absolute.

 

[Oorang]

I'd add match and countif to that list as well.

 

[RichardS]

Richard,
When you are typing in your lookup formula, just remember to hit F4 after you have entered your lookup range and your reference will change to absolute.

May be my version, but F4 doesn't work with VLOOKUP, and with SUMIF, only changes the last cell ref in the sum_range.

I like Lewiys' idea of asking which one you want though.

Cheers

Richard

 

[Domski]

If you press F4 straight after selecting the range it should make both cell references absolute however if you click on the range as an afterthought it will only apply it to the cell ref you've clicked on.

Dom