fomatting cell to decimal place

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using an exell speadsheet that uses "Vlookup" to get data from another worksheet (in the same file). I would like to format cells in the first speadsheet to specific decimal place based on a value I input into the second speadheet (eg; 0,1,2,3 decimal places). Is this possible?
 
Don't know if I understand your question but, if your Vlookup formula cell
on Sheet1 is formatted to "General", then it will return the numeric value
from the lookup list on Sheet2 with the same decimal places as the TRUE
VALUE on Sheet2.
It will *not* return the DISPLAYED value from Sheet2 *IF* the displayed
value reflects a format mask of any kind.

Your Vlookup formula cell on Sheet1 can be formatted to display the return
from Sheet2 with any format that you choose, BUT ... it *cannot copy*
formats from Sheet2.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




I am using an exell speadsheet that uses "Vlookup" to get data from another
worksheet (in the same file). I would like to format cells in the first
speadsheet to specific decimal place based on a value I input into the
second speadheet (eg; 0,1,2,3 decimal places). Is this possible?
 
I am using an exell speadsheet that uses "Vlookup" to get data from another worksheet (in the same file). I would like to format cells in the first speadsheet to specific decimal place based on a value I input into the second speadheet (eg; 0,1,2,3 decimal places). Is this possible?
Click to expand...

You have several possibilities depending on exactly what you want to do.

=TEXT(VLOOKUP(A1,rng,2),"0." & REPT("0",NumDec)) will display the result of
your VLOOKUP according to the value in NumDec (a named range).

Although this result is TEXT, it can still be used in many mathematical
computations, and it will be used as the "rounded" number.

If this is not what you require, please post something more specific.




--ron
 
Hi Ron,

As I read the OP, wouldn't the necessity of assigning a value to NumDec be
equal to simply formatting the Vlookup cell ?

Of course, feedback from the OP is needed, but I read the question as the
lookup list contained numeric data of varying decimal lengths, where perhaps
Row5 displayed 3 decimales and Row6 displayed 2 decimals, and the return to
the Vlookup formula (in one specific cell on Sheet1) was to reflect the
displayed format from Sheet2, which I believe the formula you suggested
couldn't do.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------




I am using an exell speadsheet that uses "Vlookup" to get data from another
Click to expand...
worksheet (in the same file). I would like to format cells in the first
speadsheet to specific decimal place based on a value I input into the
second speadheet (eg; 0,1,2,3 decimal places). Is this possible?

You have several possibilities depending on exactly what you want to do.

=TEXT(VLOOKUP(A1,rng,2),"0." & REPT("0",NumDec)) will display the result
of
your VLOOKUP according to the value in NumDec (a named range).

Although this result is TEXT, it can still be used in many mathematical
computations, and it will be used as the "rounded" number.

If this is not what you require, please post something more specific.




--ron
 
Hi Ron,

As I read the OP, wouldn't the necessity of assigning a value to NumDec be
equal to simply formatting the Vlookup cell ?

Of course, feedback from the OP is needed, but I read the question as the
lookup list contained numeric data of varying decimal lengths, where perhaps
Row5 displayed 3 decimales and Row6 displayed 2 decimals, and the return to
the Vlookup formula (in one specific cell on Sheet1) was to reflect the
displayed format from Sheet2, which I believe the formula you suggested
couldn't do.
Click to expand...

I obviously read the question differently <g>.

I thought he would have a separate cell to define NumDec.

If he is just interested in formatting significant digits as defined by the
entry in the LOOKUP Table, then all he would have to do is format the result
worksheet cells (where the VLOOKUP formula resides), as General.

However, if he wants to have trailing 0's based on how the data cell in the
table is formatted, (and not based on a separate cell defining NumDec), that is
more difficult. I believe, depending on the details of how things are set up,
that it could be done with the CELL worksheet function. This would assume that
the data cell is formatted either as GENERAL or as a number with a particular
number of decimal places (e.g. F5). So one could use that to produce the
format string within a TEXT worksheet function. There might be some issues
with recalculation, as changing a cell format does not trigger recalculation,
but I think it could be done.

If that's the case, it'd probably be easiest to use VBA to actually change the
format of the result cell.

Hopefully the OP will give us some guidance.


--ron
 
You bring up an interesting point about using CELL.
As an academic exercise, I think I'll play with and see if that might be a
viable approach.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi Ron,

As I read the OP, wouldn't the necessity of assigning a value to NumDec be
equal to simply formatting the Vlookup cell ?

Of course, feedback from the OP is needed, but I read the question as the
lookup list contained numeric data of varying decimal lengths, where perhaps
Row5 displayed 3 decimales and Row6 displayed 2 decimals, and the return to
the Vlookup formula (in one specific cell on Sheet1) was to reflect the
displayed format from Sheet2, which I believe the formula you suggested
couldn't do.
Click to expand...

I obviously read the question differently <g>.

I thought he would have a separate cell to define NumDec.

If he is just interested in formatting significant digits as defined by the
entry in the LOOKUP Table, then all he would have to do is format the result
worksheet cells (where the VLOOKUP formula resides), as General.

However, if he wants to have trailing 0's based on how the data cell in the
table is formatted, (and not based on a separate cell defining NumDec), that
is
more difficult. I believe, depending on the details of how things are set
up,
that it could be done with the CELL worksheet function. This would assume
that
the data cell is formatted either as GENERAL or as a number with a
particular
number of decimal places (e.g. F5). So one could use that to produce the
format string within a TEXT worksheet function. There might be some issues
with recalculation, as changing a cell format does not trigger
recalculation,
but I think it could be done.

If that's the case, it'd probably be easiest to use VBA to actually change
the
format of the result cell.

Hopefully the OP will give us some guidance.


--ron
 
Back
Top