Worksheet Linking problem

[MarkGG]

Hello I have a problem with linking my worksheets.

I have a set of seven worksheets that contain specific data and on the
eighth worksheet I want somewhat of a summary sheet. I am trying to
link the cells from the specific sheets to the summary sheet.

I am using a point and click method of doing this (click on the target
cell, press "=" and then click the target cell from another worksheet
and hit enter). This worked properly for all of the fields containing
numeric data, but the fields that have text data do not copy over they
just display the string of link, not the actual link value itself.

For example I have ='W&Ww 1'!B7 but instead of returning the proper
strign value it returns ='W&Ww 1'!B7 in the cell.

I have tried cell formatting but can't make it work, what is going on
here?

 

[MarkGG]

Does anyone have any idea at all? I am fairly good and experienced with
Excel but I haven't seen anything like this and just cannot figure it
out. It is very frustratign because it is working for all of the other
cells on the same worksheet...

Any help would be greatly greatly appreciated

 

[NlCO]

Hi, isn't the cell set as text format?
or does the cell has a ' before the formula??

Saludos

NlCO

 

[Dave Peterson]

Format the cell as general
hit F2, then enter to reenter the formula.

 

[MarkGG]

Well I managed to make it work but I had to set the fields to absolute
values so I used:

='W&Ww 7'!B$7

Anyone know why that would have to be?

 

[Guest]

WOW. I have this too! I am an advanced user as well!
This appears to be a bug as I only get this problem when I try to link to a
handful of sheets, whilst not with others! There are 20 worksheets in total.
I will do some reverse engineering to see if I can trace what is causing the
issue.
Any MVPs understand what we are talking about here and can shed some light?

 

[Dave Peterson]

Saved from a previous post...

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.