Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3-D Worksheet referencing | Excel Discussion (Misc queries) | |||
CountIF() in Worksheet B while referencing cells in Worksheet A | Excel Worksheet Functions | |||
CountIF() in Worksheet B while referencing cells in Worksheet A | Excel Worksheet Functions | |||
Referencing another worksheet | Excel Discussion (Misc queries) | |||
Referencing a worksheet name | Excel Worksheet Functions |