Use a drop down cell to copy date from one cell to another

[art27]

Hi

I don't seem to be able to get this to work. I have created a cell with a dropdown menu in it. The dropdown cell is L9. If the L9 cell says "Cash" then I want the value in cell J9 to be copied to cell M9, otherwise I want the cell M9 to stay empty.

I have added this formula into cell M9

=IF(L9="Cash",J9,"")

I cant seem to post an image but my data validation setup for cell L9 is as follows

Settings-
Allow:List
Data: between
Source: Invoice,Eftpos,Cash
ignore blank is ticked
in cell dropdown is ticked

What am I doing wrong?

 

[James006]

Hi,

From your description ... nothing is wrong ..

Do you have your Excel Workbook Calculation st to automatic ...???

 

[DanteAmor]

In M9 the cell format is General or Text.
Switch to general cell format

 

[James006]

In M9 the cell format is General or Text.
Switch to general cell format

FYI ... cell format has no impact ...

 

[higrm]

Are you sure there isn't an extra blank in your "Cash " dropdown? Remove the cell validation and test copying your "Cash" into cell L9

 

[DanteAmor]

FYI ... cell format has no impact ...

If the cell where you put the formula is in text format, simply the formula is stored as text and does not work as a formula

 

[art27]

If the cell where you put the formula is in text format, simply the formula is stored as text and does not work as a formula

Ok the workbook is set as Automatic

Cell M9 format was on number and is now on general
Cell J9 is on text but made no different when I switched it to general so its now still on text

If I remove the data validation and type cash in the cell it works fine. What could be wrong with my validation. There are 3 words separated by commas and no spaces

Invoice,Eftpos,Cash

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[art27]

I've got it working but I'm still not sure what the problem was. I replaced the validation with a new one. Same again as last time with no spaces

Invoice,Eftpos,Cash

I'm wondering if I had a space after the word cash at the end of the string? Is that what you were referring to higrm?


One other question. If I select cash on a cell and later on I decide to clear the cell because there is no data in the cells beside it, do I just use the delete key to delete the word cash or is there an option in the dropdown for a blank cell?

 

[higrm]

I'm wondering if I had a space after the word cash at the end of the string? Is that what you were referring to higrm?


One other question. If I select cash on a cell and later on I decide to clear the cell because there is no data in the cells beside it, do I just use the delete key to delete the word cash or is there an option in the dropdown for a blank cell?

Yes, that was my guess as to your problem. And yes, just delete the word, no need to make a blank entry, though that would also cause your formula to return False.