Insert Yes in place of all numbers

[zipperau2004]

Hi there Excelions
I am working on a spread sheet for house paint quoting. Sheet1 has all the calculations for time and labor. Which have formulated correctly.I have transfered to sheet 2 the price ,which is the actual quotation for the customer.

I wish to change formula =Sheet1!$B$7 from a numerical value into a word,such as yes or paint .This is so the customer only can see, that the area nominated is to be painted not the time taken to complete the painting.

All help is greatly appreciated. Oh yes I am a newbie lol

 

[JackDanIce]

Welcome to the board. Try:

=IF(ISNUMBER(Sheet1!$B$7),"YES",Sheet1$B$7)

 

[zipperau2004]

Thanks JackDanIce it worked
Is there a formula to do the whole or part of a column.
On sheet 1 B6 through to B14 have different numerical values.
On sheet 2 am I able to make the column say yes for B6 through to B14? Or do I need to calculate individually.

All help appreciated

 

[zipperau2004]

I got ahead of myself a bit, How do I stop the values on sheet 2 from displaying the word False, when there is no value added on sheet one.

regards the House Painter

 

[mikerickson]

Instead of changing all those formulas, you might want to use the custom format "Yes";"negative";""
A cell holding a positive number will display Yes, a negative number will show negative and a cell with 0 will show nothing.

 

[zipperau2004]

Thanks for the reply. Where do i customise

 

[Rick Rothstein]

Thanks for the reply. Where do i customise

Select all the cells that you want to apply this to, then right click the selection and click on "Format Cells" in the popup list that appears... this will bring up a dialog box... click the Number tab and then click on "Custom" in the list box labeled "Category"... copy/paste what mikerickson posted to you in the field directly under the label "Type", then click the OK button.