Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
I am linking one cell in worksheet B with a cell in worksheet A. I want the
cell in worksheet B to display an X if the cell in worksheet A has Oct-09 in it. In worksheet A, I have formatted the cell so that it says Oct-09 when I type in 10/2009. However, when I click on that cell in the function line at the top it displays 10/1/2009 instead of 10/2009. (I can't figure out how to change it so that it says 10/2009. Any suggestions?) In worksheet B, the function I have to get an X in that cell is: =IF('Worksheet A'!M6="Oct-09","X") The cell displays False instead of X. Also, when I change the "Oct-09" to 10/1/2009 it still says False. Please help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
When you enter "10/2009" in the cell, excel converts that to a number and displays it in date format. One option would be to format the cell M6 to text format. The change you formula from "Oct-09" to "10/2009" and you will be comparing text to text and it should work. Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
Hi
Excel converts dates to numbers so that it can manipulate them 'behind the scenes'. To do this it needs a complete date - day month and year. Where you are entering 10/2009 in sheet A, it takes this as an incomplete date and assumes that you mean the 1st of the month. To get your 'X' to display, the easy thing is to format the cell M6 on sheet A to be TEXT and enter Oct-09. Your formula on Sheet B is looking for a text string Oct-09 and will then find it. If you really want to enter your date as 10/2009, you need to change the formula on sheet B to be { =IF(WorksheetA!M6=DATEVALUE("Oct-09"),"X") } This will evaluate true for the value 10/1/2009 on sheet A and your 'X' will appear as if by magic. HTH Stu "Tflight" wrote in message ... I am linking one cell in worksheet B with a cell in worksheet A. I want the cell in worksheet B to display an X if the cell in worksheet A has Oct-09 in it. In worksheet A, I have formatted the cell so that it says Oct-09 when I type in 10/2009. However, when I click on that cell in the function line at the top it displays 10/1/2009 instead of 10/2009. (I can't figure out how to change it so that it says 10/2009. Any suggestions?) In worksheet B, the function I have to get an X in that cell is: =IF('Worksheet A'!M6="Oct-09","X") The cell displays False instead of X. Also, when I change the "Oct-09" to 10/1/2009 it still says False. Please help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
Thanks for the advice. When I changed it to TEXT it worked and when I used
the DATEVALUE function it worked. However, when I used the DATEVALUE function it didn't work when I typed Oct-09, but it did work when I typed 10/1/2009. "Stu" wrote: Hi Excel converts dates to numbers so that it can manipulate them 'behind the scenes'. To do this it needs a complete date - day month and year. Where you are entering 10/2009 in sheet A, it takes this as an incomplete date and assumes that you mean the 1st of the month. To get your 'X' to display, the easy thing is to format the cell M6 on sheet A to be TEXT and enter Oct-09. Your formula on Sheet B is looking for a text string Oct-09 and will then find it. If you really want to enter your date as 10/2009, you need to change the formula on sheet B to be { =IF(WorksheetA!M6=DATEVALUE("Oct-09"),"X") } This will evaluate true for the value 10/1/2009 on sheet A and your 'X' will appear as if by magic. HTH Stu "Tflight" wrote in message ... I am linking one cell in worksheet B with a cell in worksheet A. I want the cell in worksheet B to display an X if the cell in worksheet A has Oct-09 in it. In worksheet A, I have formatted the cell so that it says Oct-09 when I type in 10/2009. However, when I click on that cell in the function line at the top it displays 10/1/2009 instead of 10/2009. (I can't figure out how to change it so that it says 10/2009. Any suggestions?) In worksheet B, the function I have to get an X in that cell is: =IF('Worksheet A'!M6="Oct-09","X") The cell displays False instead of X. Also, when I change the "Oct-09" to 10/1/2009 it still says False. Please help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
Thanks for your help!
"TomPl" wrote: When you enter "10/2009" in the cell, excel converts that to a number and displays it in date format. One option would be to format the cell M6 to text format. The change you formula from "Oct-09" to "10/2009" and you will be comparing text to text and it should work. Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |