How can I format the cell to hold the amt to calculated with the measurement
unit in the same cell?

i.e. b27 needs to read 158 sq ft c27 is $ amt ($0.17) d27 is the formula

=(b27*c27)

When I enter in just the number 158 it calculates in d27 correctly, but when
I add sq ft after 158, d27 shows an error message #VALUE!

Thank you!

Go to FromatCellsCustom. In the type box, put ### "sq ft". The ### are
placeholders for numbers. So the data entered into the cell will calculate
and place sq ft as a display or format. The sq ft is for display or
presentation/printing.
Hope this helps.
Pat

"construction_secretary" wrote:

How can I format the cell to hold the amt to calculated with the measurement
unit in the same cell?

i.e. b27 needs to read 158 sq ft c27 is $ amt ($0.17) d27 is the formula

=(b27*c27)

When I enter in just the number 158 it calculates in d27 correctly, but when
I add sq ft after 158, d27 shows an error message #VALUE!

Thank you!

Yes that did help, thank you very much. But what if the measurement changes?
If I set b27 at Sq Ft for one customer, then the next file that I am using
the worksheet on, b27 may be linear feet. Is there a way to customize the
cell to enter the number then the measurement unit without having to format
the cell everytime?

"Pat Flynn" wrote:

Go to FromatCellsCustom. In the type box, put ### "sq ft". The ### are
placeholders for numbers. So the data entered into the cell will calculate
and place sq ft as a display or format. The sq ft is for display or
presentation/printing.
Hope this helps.
Pat

"construction_secretary" wrote:

How can I format the cell to hold the amt to calculated with the measurement
unit in the same cell?

i.e. b27 needs to read 158 sq ft c27 is $ amt ($0.17) d27 is the formula

=(b27*c27)

When I enter in just the number 158 it calculates in d27 correctly, but when
I add sq ft after 158, d27 shows an error message #VALUE!

Thank you!

Hi,
The method I used formatted the cell. You cannot change formats based on the
text extension in the cell. Have you thought about adding a helper column
next to your total column with cell validation. Datavalidationlist. Put a
list of your descriptions someplace on your spreadsheet. You can hide this
later. E.G. sq in., sq. ft, lineal ft, cu yds. So when you have a
calculation in c27, select the appropriate desc. from the drop down list. In
the next row you can place the formula =b27 & " " & c27 . This will make it
158 sq inches or whatever. Note: You will not be able to apply a formula
against this column as the data will be looked at as text.

"construction_secretary" wrote:

Yes that did help, thank you very much. But what if the measurement changes?
If I set b27 at Sq Ft for one customer, then the next file that I am using
the worksheet on, b27 may be linear feet. Is there a way to customize the
cell to enter the number then the measurement unit without having to format
the cell everytime?

"Pat Flynn" wrote:

Go to FromatCellsCustom. In the type box, put ### "sq ft". The ### are
placeholders for numbers. So the data entered into the cell will calculate
and place sq ft as a display or format. The sq ft is for display or
presentation/printing.
Hope this helps.
Pat

"construction_secretary" wrote:

How can I format the cell to hold the amt to calculated with the measurement
unit in the same cell?

i.e. b27 needs to read 158 sq ft c27 is $ amt ($0.17) d27 is the formula

=(b27*c27)

When I enter in just the number 158 it calculates in d27 correctly, but when
I add sq ft after 158, d27 shows an error message #VALUE!

Thank you!

Hi to you!

Thanks again, I did think of adding the helper column, but because it's not
the whole column I wasn't sure of how or where to insert it. Only a large
section of it needed to calculate amt needed by price per unit.

A B C D
1
2
3
4 Descr of Mtl amt needed price per unit total
5 158 sq ft $.17 a sq ft
6 51 linear ft $.24 a linear ft
7

So, starting at b5 I need another column to hold the drop down list to
select the unit of measurement. One column for the amt and one column for
unit of measurement, under b4. Does this makes sense? It's like I would be
splitting the cells from b5 to b50 in half without affecting the top portion
of Column B.


"Pat Flynn" wrote:

Hi,
The method I used formatted the cell. You cannot change formats based on the
text extension in the cell. Have you thought about adding a helper column
next to your total column with cell validation. Datavalidationlist. Put a
list of your descriptions someplace on your spreadsheet. You can hide this
later. E.G. sq in., sq. ft, lineal ft, cu yds. So when you have a
calculation in c27, select the appropriate desc. from the drop down list. In
the next row you can place the formula =b27 & " " & c27 . This will make it
158 sq inches or whatever. Note: You will not be able to apply a formula
against this column as the data will be looked at as text.

"construction_secretary" wrote:

Yes that did help, thank you very much. But what if the measurement changes?
If I set b27 at Sq Ft for one customer, then the next file that I am using
the worksheet on, b27 may be linear feet. Is there a way to customize the
cell to enter the number then the measurement unit without having to format
the cell everytime?

"Pat Flynn" wrote:

Go to FromatCellsCustom. In the type box, put ### "sq ft". The ### are
placeholders for numbers. So the data entered into the cell will calculate
and place sq ft as a display or format. The sq ft is for display or
presentation/printing.
Hope this helps.
Pat

"construction_secretary" wrote:

How can I format the cell to hold the amt to calculated with the measurement
unit in the same cell?

i.e. b27 needs to read 158 sq ft c27 is $ amt ($0.17) d27 is the formula

=(b27*c27)

When I enter in just the number 158 it calculates in d27 correctly, but when
I add sq ft after 158, d27 shows an error message #VALUE!

Thank you!