Flexible Number Formatting

E

Erin Kotch

My "master" worksheet contains links to other "division"
worksheets. The information that will be entered into the
division worksheets could be just about any format: number
with or without decimal, currency, date, or text are the
most common.

How can I format the master sheet so that whether the user
enters a date or a number, it shows up correctly on the
master?

This is something repeated on many worksheets in a shared
workbook. There are more than 2 dozen users, all with
different skill levels, so I can't expect them to make
these corrections.

Windows 2K, Excel 2K

Thank you for your help.

Erin
 
D

Dave Peterson

First, this is ugly.

There's a worksheet function that can return the format of a cell in another
workbook if that workbook is open. But if you close the workbook, it returns an
error.

The formula is:
=cell("format",a1)

But since you can't depend on this with a closed workbook, you could use a
helper cell in that "sending" workbook/worksheet (right next to the value, but
in a hidden column) that returns the format of the cell.

Say your data is in book1.xls sheet1, cell C1. In D1, you put this formula:

=cell("format",c1)

Take a look at excel's help for all the possible values returned--there are
quite a few.

In your receiving workbook/worksheet, you have one cell that returns the value:
='C:\My Documents\excel\[book3.xls]Sheet1'!$C$1
and an adjacent cell that returns the formatting
='C:\My Documents\excel\[book3.xls]Sheet1'!$D$1

Hide both those cells and then use another helper cell:

=TEXT(A1,IF(B1="G","General",IF(LEFT(B1,1)="C","$0.00",
IF(LEFT(B1,1)="D","mm/dd/yyyy","0.00"))))

I got tired after General, Currency, and Date and just went with 0.00. But you
could continue with as many as you need--until you blow up on your formula
length.

I told you it was ugly.
 
Top