Text returning a numerical value

I want to be able to type in a number in a cell and have it summed elsewhere but I'd also be a ble to enter certian text in the cells but have a specified numerical value to the cell even though the text is displayed.

Example: For a timesheet you would normally put your hours but if you take a vacation the cell would read "VACATION" but the value to the cell would be 8 and the summing would work out correctly.

Is this something that needs to be done in VBA or can Excel handle this directly?

Thanks
Ron

Originally Posted by slinger1010

I want to be able to type in a number in a cell and have it summed elsewhere but I'd also be a ble to enter certian text in the cells but have a specified numerical value to the cell even though the text is displayed.

Example: For a timesheet you would normally put your hours but if you take a vacation the cell would read "VACATION" but the value to the cell would be 8 and the summing would work out correctly.

Is this something that needs to be done in VBA or can Excel handle this directly?

Thanks
Ron

If you just have the number of hours in the cell, or the word "VACATION", you can do the following:

=SUM(IF(A2:A15="VACATION",8,A2:A15)) entered with CTRL+SHIFT+ENTER.

Scott

You can also use:

=C12+RIGHT(B12,1)

assuming that you enter the vacation in this format: Vacation 8
(number should follow the word)
change 'C12' & 'b12' to whatever cell you are trying to add