Assign value to "word" and set to show value instead of "word"

Good day,

this here is a shot in the dark but here we go>

I've created a template to calculate the overall income for a specific group that stayed at the hotel. what i want to do is, :

for every word "Single room" to have a rate 590 appear instead of the word itself, . but to have it referenced as a word for when I'm using the COUNTIF formula.

eg Single room > 590
Single room no show > 590

if i was to input it as a number, then i will not be able to break it down as to how many were "no show" and how many were not.


Is there a way to do this?, if so please, help me.

Would something like the attached work?
You can have a value entered in a cell, and have it treated as though it were something else through the lookup commands, you can't make it something that it's not though.

In the attached example a cell with the words "single room" is treated as though it has a value of 590, and the value of 590 is displayed in the cell next to it, and calculated into the last column in the table.

mew

here is an example of what i'm working on. take note of the colours. on the worksheet "THE BREAKDOWN", i need the yellow part to appear as numbers, but still be referenced by the blue part as words so it can be able to appear on the blue part of the worksheet "THE ACCOUNT".

possible?

I don't see why you need the yellow cells to become numbers - you can just store the rate adjacent to the room type in the blue section and then use a SUMPRODUCT in the total row...

Using the example file...

If for ex. K9:K18 held the rates, ie:

Please Login or Register  to view this content.

Your total formula thus becomes:

D21: =SUMPRODUCT(D$9:D$18,$K$9:$K$18)
copied across to I21


You can't really do what you want in your question given you have multiple descriptions with the same rate... if all the rates were unique you could use Named Range constants, differentiating in the blue section by the value of the Named Range rather than the name of the Named Range... but as I say given these are not unique this is not viable... the aforementioned approach is the more logical (IMO).

i hear what you're saying, thank you.
on the yellow section, my client wants to see numbers and not words, but it is helpful for me to use words when compiling the account cause it's a time saver.

option 2? can i perhaps create a workshet identical to "THE BREAKDOWN" (cloning everything entered on the first sheet), and then set it to find and replace "STD ROOM" with 590? so i can print the clone sheet to submit with the account?

That you can definitely do.
See attached.

Bah!
lol
Sort the table on your original page, the one on the right
The left-most column must be in alphabetical order for VLOOKUP to function.

That should take care of it.

mew!

Yeah! .. thank you so much. that's exactly spot on. thanx.

here's another one.
Possible?

to have the cells on the "clone" sheet to match the colour of the data entered on "the breakdown rough work" as indicated in cell range L217:M226?

eg: 'THE BREAKDOWN ROUGH WORK'!D214:F214 and 'CLONE'!D209:F209

see the attached.