If function referring to a merged cell in another worksheet

Steve'n Ayr · May 26th, 2002, 12:14 PM

I am try to enter an if function formula that will refer to a merged cell in another worksheet. The merged cell contains a drop down menu created via Data/Validation/List. The if function returns a false value when it should return a true value.
The formula I am using is
=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)
After selecting Strain/Sprain/Inflammation/Tendonitis from the drop down list the if formula still returns a false result.
Can anyone help

Thanks

Aladin Akyurek · May 26th, 2002, 12:23 PM

Quote:

On 2002-05-26 06:14, Steve'n Ayr wrote:
I am try to enter an if function formula that will refer to a merged cell in another worksheet. The merged cell contains a drop down menu created via Data/Validation/List. The if function returns a false value when it should return a true value.
The formula I am using is
=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)
After selecting Strain/Sprain/Inflammation/Tendonitis from the drop down list the if formula still returns a false result.
Can anyone help

Thanks

Make sure that 'initial interview'!f84 indeed is the cell you have dropdown list.

To check, activate the location of the dropdown list and read off the cell ref in the Name Box on the Formula Bar: the box should say F84.

Steve'n Ayr · May 26th, 2002, 12:47 PM

The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list.

Quote:

On 2002-05-26 06:23, Aladin Akyurek wrote:

Quote:

On 2002-05-26 06:14, Steve'n Ayr wrote:
I am try to enter an if function formula that will refer to a merged cell in another worksheet. The merged cell contains a drop down menu created via Data/Validation/List. The if function returns a false value when it should return a true value.
The formula I am using is
=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)
After selecting Strain/Sprain/Inflammation/Tendonitis from the drop down list the if formula still returns a false result.
Can anyone help

Thanks

Make sure that 'initial interview'!f84 indeed is the cell you have dropdown list.

To check, activate the location of the dropdown list and read off the cell ref in the Name Box on the Formula Bar: the box should say F84.

Aladin Akyurek · May 26th, 2002, 01:52 PM

The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list.

Your understanding is right.

Lets check another possibilty:

Activate the cell of your formula

=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)

and go to the Formula Bar, select the

'initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",

hit F9. If you see FALSE, while it should be TRUE, then I suspect that "Strain/Sprain/Inflammation/Tendonitis" is different in the source of your dropdown list. Check this by doing what follows:

Select first Strain/Sprain/Inflammation/Tendonitis in the dropdown list, then apply in an unused cell

=LEN('initial interview'!f84)=LEN("Strain/Sprain/Inflammation/Tendonitis")

If this gives false, they are indeed entered differently.

[ This Message was edited by: Aladin Akyurek on 2002-05-26 07:53 ]

Steve'n Ayr · May 26th, 2002, 02:37 PM

Quote:

On 2002-05-26 07:52, Aladin Akyurek wrote:

The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list.

Your understanding is right.

Lets check another possibilty:

Activate the cell of your formula

=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)

and go to the Formula Bar, select the

'initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",

hit F9. If you see FALSE, while it should be TRUE, then I suspect that "Strain/Sprain/Inflammation/Tendonitis" is different in the source of your dropdown list. Check this by doing what follows:

Select first Strain/Sprain/Inflammation/Tendonitis in the dropdown list, then apply in an unused cell

=LEN('initial interview'!f84)=LEN("Strain/Sprain/Inflammation/Tendonitis")

If this gives false, they are indeed entered differently.

[ This Message was edited by: Aladin Akyurek on 2002-05-26 07:53 ]

Steve'n Ayr · May 26th, 2002, 02:39 PM

Quote:

On 2002-05-26 07:52, Aladin Akyurek wrote:

The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list.

Your understanding is right.

Lets check another possibilty:

Activate the cell of your formula

=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)

and go to the Formula Bar, select the

'initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",

hit F9. If you see FALSE, while it should be TRUE, then I suspect that "Strain/Sprain/Inflammation/Tendonitis" is different in the source of your dropdown list. Check this by doing what follows:

Select first Strain/Sprain/Inflammation/Tendonitis in the dropdown list, then apply in an unused cell

=LEN('initial interview'!f84)=LEN("Strain/Sprain/Inflammation/Tendonitis")

If this gives false, they are indeed entered differently.

[ This Message was edited by: Aladin Akyurek on 2002-05-26 07:53 ]

I have done as you suggested and both return a false. The text in the drop down list and the text in the formula are the same. I have tried with a number of different items from the drop down list and the result is alway falxe.

I'm stumped!!

Thanks for your help

Aladin Akyurek · May 26th, 2002, 02:51 PM

Quote:

On 2002-05-26 08:39, Steve'n Ayr wrote:

Quote:

On 2002-05-26 07:52, Aladin Akyurek wrote:

The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list.

Your understanding is right.

Lets check another possibilty:

Activate the cell of your formula

=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)

and go to the Formula Bar, select the

'initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",

hit F9. If you see FALSE, while it should be TRUE, then I suspect that "Strain/Sprain/Inflammation/Tendonitis" is different in the source of your dropdown list. Check this by doing what follows:

Select first Strain/Sprain/Inflammation/Tendonitis in the dropdown list, then apply in an unused cell

=LEN('initial interview'!f84)=LEN("Strain/Sprain/Inflammation/Tendonitis")

If this gives false, they are indeed entered differently.

[ This Message was edited by: Aladin Akyurek on 2002-05-26 07:53 ]

I have done as you suggested and both return a false. The text in the drop down list and the text in the formula are the same. I have tried with a number of different items from the drop down list and the result is alway falxe.

I'm stumped!!

Thanks for your help

At least we have the diagnosis.

Try:

First, select Strain/Sprain/Inflammation/Tendonitis from the dropdown list in F84, then use:

=IF(TRIM('initial interview'!F84)="Strain/Sprain/Inflammation/Tendonitis",1,4)

What is the result?

Steve'n Ayr · May 27th, 2002, 12:57 AM

Quote:

On 2002-05-26 08:51, Aladin Akyurek wrote:

Quote:

On 2002-05-26 08:39, Steve'n Ayr wrote:

Quote:

On 2002-05-26 07:52, Aladin Akyurek wrote:

The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list.

Your understanding is right.

Lets check another possibilty:

Activate the cell of your formula

=if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4)

and go to the Formula Bar, select the

'initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",

hit F9. If you see FALSE, while it should be TRUE, then I suspect that "Strain/Sprain/Inflammation/Tendonitis" is different in the source of your dropdown list. Check this by doing what follows:

Select first Strain/Sprain/Inflammation/Tendonitis in the dropdown list, then apply in an unused cell

=LEN('initial interview'!f84)=LEN("Strain/Sprain/Inflammation/Tendonitis")

If this gives false, they are indeed entered differently.

[ This Message was edited by: Aladin Akyurek on 2002-05-26 07:53 ]

I have done as you suggested and both return a false. The text in the drop down list and the text in the formula are the same. I have tried with a number of different items from the drop down list and the result is alway falxe.

I'm stumped!!

Thanks for your help

At least we have the diagnosis.

Try:

First, select Strain/Sprain/Inflammation/Tendonitis from the dropdown list in F84, then use:

=IF(TRIM('initial interview'!F84)="Strain/Sprain/Inflammation/Tendonitis",1,4)

What is the result?

The result of this equation is 1, the True (correct) response. How do I get it to work in the equation?

Aladin Akyurek · May 27th, 2002, 03:57 AM

The result of this equation is 1, the True (correct) response. How do I get it to work in the equation?

=IF(TRIM('initial interview'!F84)="Strain/Sprain/Inflammation/Tendonitis",1,4)

However, it would be much better if you'd get rid of the extra spaces around entries of the source list that you use for the dropdown list.

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 26th, 2002, 12:14 PM	#1
Steve'n Ayr Join Date: May 2002 Posts: 14	I am try to enter an if function formula that will refer to a merged cell in another worksheet. The merged cell contains a drop down menu created via Data/Validation/List. The if function returns a false value when it should return a true value. The formula I am using is =if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4) After selecting Strain/Sprain/Inflammation/Tendonitis from the drop down list the if formula still returns a false result. Can anyone help Thanks

May 26th, 2002, 01:52 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 36,221	The cell reference is F84, my understanding is that the cell reference of merged cells is always the cell in the top left hand corner. In my case if merged F84, G84 and H84 to make a cell large enough to accommodate the longer items on the list. Your understanding is right. Lets check another possibilty: Activate the cell of your formula =if('initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis",1,4) and go to the Formula Bar, select the 'initial interview'!f84="Strain/Sprain/Inflammation/Tendonitis", hit F9. If you see FALSE, while it should be TRUE, then I suspect that "Strain/Sprain/Inflammation/Tendonitis" is different in the source of your dropdown list. Check this by doing what follows: Select first Strain/Sprain/Inflammation/Tendonitis in the dropdown list, then apply in an unused cell =LEN('initial interview'!f84)=LEN("Strain/Sprain/Inflammation/Tendonitis") If this gives false, they are indeed entered differently. [ This Message was edited by: Aladin Akyurek on 2002-05-26 07:53 ]

May 27th, 2002, 03:57 AM	#9
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 36,221	The result of this equation is 1, the True (correct) response. How do I get it to work in the equation? =IF(TRIM('initial interview'!F84)="Strain/Sprain/Inflammation/Tendonitis",1,4) However, it would be much better if you'd get rid of the extra spaces around entries of the source list that you use for the dropdown list. Aladin