How do you know TM is a he and not a she and shouldn't it be Toothless Mama?

Anyway it was discovered earlier, I probably saw it here back in 2000
something.

I use it all the time and I have for a long time, if I want to hardcode
let's say a grade setup in vlookup I just create a table first, then I put
in the grades and their values and what looks like.


=VLOOKUP(A1,F3:G8,2,0)

will look like

=VLOOKUP(A1,{0,"F";55,"D";65,"C";75,"B";85,"A+";95 ,"A+"},2,0)


after I have highlighted F3:G8 and pressed F9, that way a poster can just
copy the formula directly and just change A1
Same when I build complicated formulas where I can select parts of the
formula to find out where the problem is, that is much easier than using the
built in auditing tool. The way out is to use the escape key otherwise it
will be fixed


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote in
message ...
Learn something new everyday, and I doubled checked just to see if that
was in Excel 2002 as well as Excel 2007 and it was.
Thanks for pointing that out, that use of F9 is certainly not intuitive
but I doubt that it would cause accidental problems.

It was a new one for me too! Remember, this is Teethless mama's discovery,
although he doesn't mention how long ago it was that he discovered it.

Incidentally, often such entries can be automated to create
the timestamp when the adjacent cell is updated, so much
less work using an Change Event macro if that sheet gets
a lot of new entries. The user would have to change the code
but something along the lines of
http://www.mvps.org/dmcritchie/excel...#datetimestamp

True, but as Harlan has pointed out to me a couple of times in the past,
macro solutions are not always desirable.

Rick