EXCEL - Why doesn't the formula of =(First Worksheet!C2) work?

[mdcr]

We are in a certain cell on the second worksheet of an Excel spreadsheet and we are trying to link to the first worksheet (trying to pull the label in a certain cell on the first worksheet onto the second worksheet). To do this, we select the cell on the second worksheet, type '=', then click the tab on the bottom to get to the first worksheet, click the specific cell, and hit Enter. Now, the resulting formula on the second worksheet should display as '=(First Worksheet!C2)' without the quotes, right? And, the resulting data on the second worksheet should display the information or name that is on the first worksheet in cell C2, right? Well, the formula is not working and we had wondered if it was because of the two words in the first worksheet's name, but even when we changed the name to a single word, it still doesn't work. Any ideas? Common error in Excel XP? Simple problem, hopefully, but we are stuck...

 

[Lilliabeth]

Do other formulas on this worksheet work?

 

[SkipVought]

Hi,

When you have SPACES in your workbook name/sheet name...
[tt]
='Sheet one'!E2
[/tt]
BUT...

you should NEVER be typing in these references!!!!

NEVER!!!

Quoth the raven, "NEVERMORE!"

Just use the POINT 'n' CLICK method and you'll NEVER be wrong with a reference format.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[Lilliabeth]

Skip, It sounds to me like they did it correctly...

we select the cell on the second worksheet, type '=', then click the tab on the bottom to get to the first worksheet, click the specific cell, and hit Enter.

What they are execting the formula to look like is a bit wrong, though (no parentheses), but it still should work.

 

[mdcr]

Yes, separate formulas on the same page work, and as for Skip's post, my bad - it actually is named 'First Worksheet'!C2 it actually has single quotes around it....We haven't tried detect and repair yet....

 

[SkipVought]

in Tools/Options - View Tab - Window options -- UNCHECK Formulas


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[SkipVought]

AND...

make sure that the CELL FORMAT is NOT TEXT


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[mdcr]

Okay, so the format was TEXT, but we changed it to General, and the formula worked fine....But when we changed something on the worksheet (unrelated to the formatting) and then refreshed the worksheet, it reverted back to Text, so we're still not sure why it's happening....

 

[SkipVought]

"...But when we changed something on the worksheet..."

What! Is this a guessing game?

Do we get 20 questions?

1) What is the format of that cell, now that, "...But when we changed something on the worksheet..."

19 left.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[mdcr]

When we typed on a new line, or if we add a formula on the bottom of the worksheet, like a sum statement; it in no way is related to the problem formula. Basically anytime after we hit the refresh button, the formula would revert back to Text....

 

[SkipVought]

I do not have a "Refresh Button" on my worksheet.

What does your "Refresh Button" do? What kind of button is it -- MS Form, Control Toolbox, ToolBar?

Does it run a macro? If so, please post the code.

When you changed the format from TEXT to WHAT, (GENERAL?) did you do that for ONE cell or a ROW or a COLUMN or WHAT?


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[mdcr]

It is in a toolbar from an add-in from an accounting program, Prosystems fx, and in the toolbar is a button that is called Refresh. It is for recalculating financial formulas but I don't have the code. But if we don't use that button, and just make the change, save the file, close it, and then reopen it, the format of the cell goes back to General. We have gone to that cell and manually changed it to Text, not the whole row or any other range of cells.

 

[SkipVought]

"We have gone to that cell and manually changed it to Text"

Why are you changing the format to TEXT?

That's the problem!


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[mdcr]

SORRY - We manually change it to General and it goes back to Text, I juxtaposed the terms

 

[SkipVought]

And it ONLY formats as text AFTER the Refresh Button?

If so, then that's your culpret.

Right click the button and select Assign Macro... and see what macro has been assigned.

Edit the macro, copy the code and paste back here.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[mdcr]

Well, we can't select the Assign Macro, it just shows us the available toolbars on the worksheet (Standard, Formatting, etc), is there somewhere else where we should be clicking? Thanks

 

[SkipVought]

Right click in the tool bar and select Customize...

THEN right click the button and proceed.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[mdcr]

Well, after a delay, I was able to see the macro that the button calls; it is called Refresh_OnClick, but when we go to the visual basic editor and try to open the ePace toolbar (titled epace.xla), it prompts us for a password. Is this proprietary, or are we looking in the wrong area altogether?

 

[SkipVought]

Well, whoever wrote the macro, has the VB Editor protected. You either have to get the password from "whoever" or yer up the creek!

Skip,
[sub]
[red]Be Advised![/red]
Alcohol and Calculus do not mix!
If you DRINK, don't DERIVE![/sub]