Excel 2007 : Lookup:create a formular

**Shazz** · 10-20-2009, 05:43 AM

Can anyone help me, I am trying to create a formular that if I type a particular value certain text and colour is displayed, I have attached the Excel Sheet to this post with the information in it.

Thanks in advance.

Shazz

x

**Richard Buttrey** · 10-20-2009, 06:08 AM

Hi,

Your request is ambiguous. First do you really mean D1?
Second, if you type £38 in D1 as your note suggests, D1 can't therefore contain text like 'Paid in Full'.

I suspect you're trying to identify the text 'Paid in Full' in B2 if C2 = £38. In which case B2 would be :

Please Login or Register  to view this content.

However you need to clarify what you're wanting to achieve.

Rgds

**Shazz** · 10-20-2009, 06:16 AM

It does not have to be D1, I was using D1 as an example becasue I had no forumular in the spreadsheet.

I willl try and explain, if I typed for example £38.00 in the Cell that contains the formular then "Paid in Full" and relevant colour would be displayed, if I typed £00.00 in the cell thta contains the formular then "Awaiting Payment" and relevant colour would be displayed and so on.

Shazz

**Palmetto** · 10-20-2009, 07:25 AM

I willl try and explain, if I typed for example £38.00 in the Cell that contains the formular then "Paid in Full" and relevant colour would be displayed, if I typed £00.00 in the cell thta contains the formular then "Awaiting Payment" and relevant colour would be displayed and so on.

What you are asking above is impossible. If you type a value into a cell that contains a formula, then the formula is overwritten by the value and ceases to exist. A cell can contain either a directly entered value OR a formula that returns a result, but not both.

If you are wanting to return a matching result for "£38.00" (or other number), then you could use a VLOOKUP function. To apply a color to the cell use a Conditional Formatting using the formula option set to look at the result of the lookup and match the color accordingly.

**Shazz** · 10-20-2009, 07:38 AM

I am really rubbish at explaining things.

You are exactly right, I want to type a value into a cell and in the cell where the formula is it will put the text that it is relevent to.

I am rerally sorry for being such a twit

Shazz