Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting Cell Number Format With A Worksheet Function
Excel 2003, SP2 win Win XP, SP2
Trying to use a VLookup to present user-selectable information from an Excel range. The range contains large and small numbers, percents and currency values in it - and VLookup doesn't seem to return the cell number formatting of the "looked-up" cell. I've kludged a way to get the formatting from the looked-up cell and apply it to the cell doing the lookup, but what I'm doing converts the looked-up cell's value to a text (I'm using the TEXT worksheet function) - and that keeps me from doing summations, etc. Is there any way to set a cell's number format with a combination of built-in or user-defined worksheet functions that can co-exist with a VLookup, HLookup or Match function? Thanks in advance for any help! James |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting Cell Number Format With A Worksheet Function
There's no way to set a cell format with a function.
There is a workaround to what you are doing, though. Lets say your range of returned values is B1:B100 (the results of the VLOOKUP's). Whenever you call that range for a calculation, included it within the VALUE() function. For example: =SUM(VALUE(B1:B100)) And then commit it with CTRL+SHIFT+ENTER as it is an array function. This way you can still do calculations on the text values in this range. -- Regards, Dave " wrote: Excel 2003, SP2 win Win XP, SP2 Trying to use a VLookup to present user-selectable information from an Excel range. The range contains large and small numbers, percents and currency values in it - and VLookup doesn't seem to return the cell number formatting of the "looked-up" cell. I've kludged a way to get the formatting from the looked-up cell and apply it to the cell doing the lookup, but what I'm doing converts the looked-up cell's value to a text (I'm using the TEXT worksheet function) - and that keeps me from doing summations, etc. Is there any way to set a cell's number format with a combination of built-in or user-defined worksheet functions that can co-exist with a VLookup, HLookup or Match function? Thanks in advance for any help! James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
I want to format a number cell without the decimal and without ro. | Excel Discussion (Misc queries) | |||
#VALUE in cell but pop up function box show right number | Excel Discussion (Misc queries) |