using a data validation drop down list AND an IF formula in the same cell

**bite_tony** · 04-05-2013, 08:28 AM

hi, I have a spreadsheet in excel 2010 where one of my columns uses a dropdown list using the 'data validation' function.
The list details several payment methods. Certain customers always use the same payment method, so I had planned to use an IF formula to autofill the field with a particular payment method to save me time.
For example, Mr Smith always pays by Direct Debit.

However, even though 'direct debit' appears in the drop down list I have made, Excel wont let me input the formula in that cell...

Is there any way around this?
thanks for your help

**AndyLitch** · 04-05-2013, 08:54 AM

If you're going to calculate the value, you don't really need validation ...How about using conditional formatting instead ?

**bite_tony** · 04-05-2013, 09:00 AM

im not always going to calculate the value..
only for two or three regular customers..
we have lots of other customers who all use different ways to pay..

basically, data in this spreadsheet is copied and pasted from another sheet, sometimes 200 rows of data can be dumped at once, I just thought itd save me time if excel picked off the straight forward ones by itself, then I could use the drop down list to populate the others.

**bite_tony** · 04-05-2013, 09:17 AM

!!!
I fixed it!!!

just thought I would put it here incase anyone else is wondering

basically, all I had to do was make sure the conditions were in place for the IF formula to be activated, when I input the formula.

so, if Mr Smith always pays by direct debit, and direct debit appears in my validation list, Excel let me put the If formula in as long as I made sure the row I was inputting it into was a row where Mr smith was paying by direct debit.

that was a terrible sentence, I hope it made sense

**AndyLitch** · 04-05-2013, 09:23 AM

Perfect sense .... did you manage to copy and paste it through the dataset ?

**jazmin2014** · 04-16-2014, 03:59 PM

Thank you for sharing how you solved it!! I had the same problem and it worked! Yay!

**Sybon** · 06-23-2021, 02:11 AM

Hi.. Could u please share the excel file? It will be very helpful for me

**Glenn Kennedy** · 06-23-2021, 02:59 AM

Thisthread is 8 YEARS old!!!

Hi.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.

I am closing this thread NOW.