Referencing a value on a different worksheet
 

I have a form in Excel.
The second (work)sheet of this form references a field from the first
(work)sheet. (Specifically the name.)
So I enter the "=" and then I click on the first (work)sheet and click on
the cell I want to reference and click "enter" and this is what I get on the
second sheet.
='07PAGE-1'!B7:I7

It doesn't resolve itself into the name.

Why not?
Clicking on the accompanying error message it seems possible that it doesn't
work because the cells (both the source and the destination) have been merged.

Is that what's wrong? If so is there a workaround?

Is there a different mistake?

Thanks.

Referencing a value on a different worksheet
 

When referencing a merged cell, only reference the top-left most cell in the
merged area. So, in your case:

='07PAGE-1'!B7

HTH,
Elkar


"David Gerstman" wrote:

I have a form in Excel.
The second (work)sheet of this form references a field from the first
(work)sheet. (Specifically the name.)
So I enter the "=" and then I click on the first (work)sheet and click on
the cell I want to reference and click "enter" and this is what I get on the
second sheet.
='07PAGE-1'!B7:I7

It doesn't resolve itself into the name.

Why not?
Clicking on the accompanying error message it seems possible that it doesn't
work because the cells (both the source and the destination) have been merged.

Is that what's wrong? If so is there a workaround?

Is there a different mistake?

Thanks.

Referencing a value on a different worksheet
 

Elkar,

Thank you for the response, but that did not do the trick.

I still get the formula displayed and not the value.

David

"Elkar" wrote:

When referencing a merged cell, only reference the top-left most cell in the
merged area. So, in your case:

='07PAGE-1'!B7

HTH,
Elkar

Referencing a value on a different worksheet
 

Ok, I misunderstood. This is probably one of two things then. Either your
cell is pre-formatted as text, thus formulas entered will be treated as text
strings. Or, your sheet settings are set to display formulas.

First off, try pre-formatting the cell as "General" then enter your formula.

If that doesn't solve the problem, then try:

In Excel 2003 or earilier:
From the TOOLS Menu, choos "Options"
On the VIEW Tab, uncheck "Formulas"
Click OK

In Excel 2007:
Click on the OFFICE Button
Click on the "Excel Options" Button
Click on the "Advanced" Tab
Scroll down to the "Display Options for this Worksheet" section
Uncheck "Show formulas in cells" option
Click OK

HTH,
Elkar


"David Gerstman" wrote:

Elkar,

Thank you for the response, but that did not do the trick.

I still get the formula displayed and not the value.

David

"Elkar" wrote:

When referencing a merged cell, only reference the top-left most cell in the
merged area. So, in your case:

='07PAGE-1'!B7

HTH,
Elkar

Referencing a value on a different worksheet
 

Elkar,

I'm sorry. (And sorry about my lack of clarity.)
But neither of those solutions (I'm using Excel) 2003 is doing it.

David

"Elkar" wrote:

Ok, I misunderstood. This is probably one of two things then. Either your
cell is pre-formatted as text, thus formulas entered will be treated as text
strings. Or, your sheet settings are set to display formulas.

First off, try pre-formatting the cell as "General" then enter your formula.

If that doesn't solve the problem, then try:

In Excel 2003 or earilier:
From the TOOLS Menu, choos "Options"
On the VIEW Tab, uncheck "Formulas"
Click OK

In Excel 2007:
Click on the OFFICE Button
Click on the "Excel Options" Button
Click on the "Advanced" Tab
Scroll down to the "Display Options for this Worksheet" section
Uncheck "Show formulas in cells" option
Click OK

HTH,
Elkar


"David Gerstman" wrote:

Elkar,

Thank you for the response, but that did not do the trick.

I still get the formula displayed and not the value.

David

"Elkar" wrote:

When referencing a merged cell, only reference the top-left most cell in the
merged area. So, in your case:

='07PAGE-1'!B7

HTH,
Elkar

Referencing a value on a different worksheet
 

Hmm... I've got to think that the problem lies with cell formatting.

Select the cell on your second worksheet where you're entering the formula.
From the FORMAT Menu, choose "Cells"
On the Number Tab, choose "General"
Click OK
Type in =
Select your first worksheet, then click on the cell to be referenced
Hit ENTER

That should work. Is it still displaying the formula instead of the
results? If so, I'm really at a loss as to why.

HTH,
Elkar


"David Gerstman" wrote:

Elkar,

I'm sorry. (And sorry about my lack of clarity.)
But neither of those solutions (I'm using Excel) 2003 is doing it.

David

"Elkar" wrote:

Ok, I misunderstood. This is probably one of two things then. Either your
cell is pre-formatted as text, thus formulas entered will be treated as text
strings. Or, your sheet settings are set to display formulas.

First off, try pre-formatting the cell as "General" then enter your formula.

If that doesn't solve the problem, then try:

In Excel 2003 or earilier:
From the TOOLS Menu, choos "Options"
On the VIEW Tab, uncheck "Formulas"
Click OK

In Excel 2007:
Click on the OFFICE Button
Click on the "Excel Options" Button
Click on the "Advanced" Tab
Scroll down to the "Display Options for this Worksheet" section
Uncheck "Show formulas in cells" option
Click OK

HTH,
Elkar


"David Gerstman" wrote:

Elkar,

Thank you for the response, but that did not do the trick.

I still get the formula displayed and not the value.

David

"Elkar" wrote:

When referencing a merged cell, only reference the top-left most cell in the
merged area. So, in your case:

='07PAGE-1'!B7

HTH,
Elkar

Referencing a value on a different worksheet
 

I also figured that it was formatting, but I followed what you said and still
get a #VALUE! error.
(Does it make a difference that both cells are merged?)

Sorry to keep bugging you, I really appreciate the help.

David

"Elkar" wrote:

Hmm... I've got to think that the problem lies with cell formatting.

Select the cell on your second worksheet where you're entering the formula.
From the FORMAT Menu, choose "Cells"
On the Number Tab, choose "General"
Click OK
Type in =
Select your first worksheet, then click on the cell to be referenced
Hit ENTER

That should work. Is it still displaying the formula instead of the
results? If so, I'm really at a loss as to why.

HTH,
Elkar

Referencing a value on a different worksheet
 

Ok, good, you're getting a #VALUE! error. That's progress. At least that
means that the formula is displaying results now.

So, yes, I believe this gets back to my original response, in making sure
that the reference to a merged cell only includes the top-left most cell in
the merged area.

If you click on the cell, then look at the formula bar, what is the formula?
If it is ='07PAGE-1'!B7:I7 like you originally posted, then remove the :I7
portion.

HTH,
Elkar


"David Gerstman" wrote:

I also figured that it was formatting, but I followed what you said and still
get a #VALUE! error.
(Does it make a difference that both cells are merged?)

Sorry to keep bugging you, I really appreciate the help.

David

"Elkar" wrote:

Hmm... I've got to think that the problem lies with cell formatting.

Select the cell on your second worksheet where you're entering the formula.
From the FORMAT Menu, choose "Cells"
On the Number Tab, choose "General"
Click OK
Type in =
Select your first worksheet, then click on the cell to be referenced
Hit ENTER

That should work. Is it still displaying the formula instead of the
results? If so, I'm really at a loss as to why.

HTH,
Elkar

Referencing a value on a different worksheet
 

When I remove the :I7 it now displays
='07PAGE-1'!B7 as the value.

I'll double check the Cell format.

David

"Elkar" wrote:

Ok, good, you're getting a #VALUE! error. That's progress. At least that
means that the formula is displaying results now.

So, yes, I believe this gets back to my original response, in making sure
that the reference to a merged cell only includes the top-left most cell in
the merged area.

If you click on the cell, then look at the formula bar, what is the formula?
If it is ='07PAGE-1'!B7:I7 like you originally posted, then remove the :I7
portion.

HTH,
Elkar


"David Gerstman" wrote:

I also figured that it was formatting, but I followed what you said and still
get a #VALUE! error.
(Does it make a difference that both cells are merged?)

Sorry to keep bugging you, I really appreciate the help.

David

"Elkar" wrote:

Hmm... I've got to think that the problem lies with cell formatting.

Select the cell on your second worksheet where you're entering the formula.
From the FORMAT Menu, choose "Cells"
On the Number Tab, choose "General"
Click OK
Type in =
Select your first worksheet, then click on the cell to be referenced
Hit ENTER

That should work. Is it still displaying the formula instead of the
results? If so, I'm really at a loss as to why.

HTH,
Elkar

Referencing a value on a different worksheet
 

Ok, now I think I see whats going on. When you use the mouse to click on the
cell to create the reference, it carries over the TEXT format as well. The
formula gets created properly as a formula, but contains an incorrect cell
referrence. Then, any subsequent changes to the formula use the TEXT
formatting that was carried over.

Now, once you've remove the :I7 portion, change the format back to
"General". But, this time, instead of re-entering the formula, just hit F2
to edit the formula, then Enter. This should reapply the formula using the
"General" format.

For future references to merged cells, it may be better to just type in the
formula rather than using the mouse to select the cell.

HTH,
Elkar


"David Gerstman" wrote:

When I remove the :I7 it now displays
='07PAGE-1'!B7 as the value.

I'll double check the Cell format.

David

"Elkar" wrote:

Ok, good, you're getting a #VALUE! error. That's progress. At least that
means that the formula is displaying results now.

So, yes, I believe this gets back to my original response, in making sure
that the reference to a merged cell only includes the top-left most cell in
the merged area.

If you click on the cell, then look at the formula bar, what is the formula?
If it is ='07PAGE-1'!B7:I7 like you originally posted, then remove the :I7
portion.

HTH,
Elkar


"David Gerstman" wrote:

I also figured that it was formatting, but I followed what you said and still
get a #VALUE! error.
(Does it make a difference that both cells are merged?)

Sorry to keep bugging you, I really appreciate the help.

David

"Elkar" wrote:

Hmm... I've got to think that the problem lies with cell formatting.

Select the cell on your second worksheet where you're entering the formula.
From the FORMAT Menu, choose "Cells"
On the Number Tab, choose "General"
Click OK
Type in =
Select your first worksheet, then click on the cell to be referenced
Hit ENTER

That should work. Is it still displaying the formula instead of the
results? If so, I'm really at a loss as to why.

HTH,
Elkar

Referencing a value on a different worksheet
 

Elkar,

That did it! Thanks so much.
It was for a superior at work so I really appreciate it.
(Yes, I credited you. He knows I got help. He has a print out of the correct
answer.)
David

"Elkar" wrote:

Ok, now I think I see whats going on. When you use the mouse to click on the
cell to create the reference, it carries over the TEXT format as well. The
formula gets created properly as a formula, but contains an incorrect cell
referrence. Then, any subsequent changes to the formula use the TEXT
formatting that was carried over.

Now, once you've remove the :I7 portion, change the format back to
"General". But, this time, instead of re-entering the formula, just hit F2
to edit the formula, then Enter. This should reapply the formula using the
"General" format.

For future references to merged cells, it may be better to just type in the
formula rather than using the mouse to select the cell.

HTH,
Elkar

Referencing a value on a different worksheet
 

Thanks for the recognition. I'm just happy to help! :)
Elkar

"David Gerstman" wrote:

Elkar,

That did it! Thanks so much.
It was for a superior at work so I really appreciate it.
(Yes, I credited you. He knows I got help. He has a print out of the correct
answer.)
David

"Elkar" wrote:

Ok, now I think I see whats going on. When you use the mouse to click on the
cell to create the reference, it carries over the TEXT format as well. The
formula gets created properly as a formula, but contains an incorrect cell
referrence. Then, any subsequent changes to the formula use the TEXT
formatting that was carried over.

Now, once you've remove the :I7 portion, change the format back to
"General". But, this time, instead of re-entering the formula, just hit F2
to edit the formula, then Enter. This should reapply the formula using the
"General" format.

For future references to merged cells, it may be better to just type in the
formula rather than using the mouse to select the cell.

HTH,
Elkar