The cell format codes returned by the CELL function does not work inside of a TEXT function.
For example, the contents of my cells are:
A1: 50000
A2: =CELL("format",A1)
A3: =TEXT(A1,CELL("format",A1))
A4: =TEXT(A1,".00")
and the cells return (with comments):
A1: 50000.00 (cell is formatted as a 2-decimal number)
A2: F2 (the cell format code for 2-decimal number)
A3: F2 (does not return correct value "50000.00")
A4: 50000.00 (so..."F2" is not the same a ".00")
So, how do I do this?
Conditional formatting won't work because you only get 3 conditions.
A manual recalc (F9 key) didn't fix it.
VBA or Macros won't work because no one will launch or hit button (etc.)
We need this because the person entering data may need to change the entry cells to different formats for different types of checks (payroll vs. reimbursement vs. vendor check...etc).
We need to automatically change the format of the output cells to match the format of the entry cells (which the person is allowed to change the format of).
Again, conditional formatting won't work...too few choices. And we don't know how many different formats we will need.
Thanks
Tony
For example, the contents of my cells are:
A1: 50000
A2: =CELL("format",A1)
A3: =TEXT(A1,CELL("format",A1))
A4: =TEXT(A1,".00")
and the cells return (with comments):
A1: 50000.00 (cell is formatted as a 2-decimal number)
A2: F2 (the cell format code for 2-decimal number)
A3: F2 (does not return correct value "50000.00")
A4: 50000.00 (so..."F2" is not the same a ".00")
So, how do I do this?
Conditional formatting won't work because you only get 3 conditions.
A manual recalc (F9 key) didn't fix it.
VBA or Macros won't work because no one will launch or hit button (etc.)
We need this because the person entering data may need to change the entry cells to different formats for different types of checks (payroll vs. reimbursement vs. vendor check...etc).
We need to automatically change the format of the output cells to match the format of the entry cells (which the person is allowed to change the format of).
Again, conditional formatting won't work...too few choices. And we don't know how many different formats we will need.
Thanks
Tony