Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

Try Tools Options View tab under the Windows Options uncheck the Formulas
box

Elisabeth

"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

Roger, I once had a similar problem which turned out to simply be that I had
pressed Ctrl key and the key to the left of number 1 before saving my file.
This combination 'shows' all formulae. When I opened it up next time it still
showed all the formulae. Pressing this combo might help.
Steve Thompson
"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

Thanks for the replies, have tried both suggestions and they do replicate the
problem across the whole worksheet, but the problem I have is trying to edit
the cell location of the data source for individual cells, as i say if I try
and edit the data so that the source location is different E.g. changing data
source from =data!A100 to =data!A150 then the location (=data!A150) appears
rather than the data in that location.

"jinkysteve" wrote:

Roger, I once had a similar problem which turned out to simply be that I had
pressed Ctrl key and the key to the left of number 1 before saving my file.
This combination 'shows' all formulae. When I opened it up next time it still
showed all the formulae. Pressing this combo might help.
Steve Thompson
"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

The cell may be formatted as text. Try changing it to General and then
select the cell and press F2 and then ENTER.

Elisabeth

"Roger Thompson" wrote:

Thanks for the replies, have tried both suggestions and they do replicate the
problem across the whole worksheet, but the problem I have is trying to edit
the cell location of the data source for individual cells, as i say if I try
and edit the data so that the source location is different E.g. changing data
source from =data!A100 to =data!A150 then the location (=data!A150) appears
rather than the data in that location.

"jinkysteve" wrote:

Roger, I once had a similar problem which turned out to simply be that I had
pressed Ctrl key and the key to the left of number 1 before saving my file.
This combination 'shows' all formulae. When I opened it up next time it still
showed all the formulae. Pressing this combo might help.
Steve Thompson
"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

Well done Elisabeth, your advice has solved the problem completely. I had
tried reformatting the cells to General but had not pressed F2, what does
pressing F2 actually do?

"Elisabeth D" wrote:

The cell may be formatted as text. Try changing it to General and then
select the cell and press F2 and then ENTER.

Elisabeth

"Roger Thompson" wrote:

Thanks for the replies, have tried both suggestions and they do replicate the
problem across the whole worksheet, but the problem I have is trying to edit
the cell location of the data source for individual cells, as i say if I try
and edit the data so that the source location is different E.g. changing data
source from =data!A100 to =data!A150 then the location (=data!A150) appears
rather than the data in that location.

"jinkysteve" wrote:

Roger, I once had a similar problem which turned out to simply be that I had
pressed Ctrl key and the key to the left of number 1 before saving my file.
This combination 'shows' all formulae. When I opened it up next time it still
showed all the formulae. Pressing this combo might help.
Steve Thompson
"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

F2 allows you to edit the contents of the cell and in this case it also
"refreshes" the cell to conform to the new formatting. It seems like
something that should automatically happen without the F2 intervention, and
yet...just one of the quirks we have to live with I guess.

Glad it worked.

Elisabeth

"Roger Thompson" wrote:

Well done Elisabeth, your advice has solved the problem completely. I had
tried reformatting the cells to General but had not pressed F2, what does
pressing F2 actually do?

"Elisabeth D" wrote:

The cell may be formatted as text. Try changing it to General and then
select the cell and press F2 and then ENTER.

Elisabeth

"Roger Thompson" wrote:

Thanks for the replies, have tried both suggestions and they do replicate the
problem across the whole worksheet, but the problem I have is trying to edit
the cell location of the data source for individual cells, as i say if I try
and edit the data so that the source location is different E.g. changing data
source from =data!A100 to =data!A150 then the location (=data!A150) appears
rather than the data in that location.

"jinkysteve" wrote:

Roger, I once had a similar problem which turned out to simply be that I had
pressed Ctrl key and the key to the left of number 1 before saving my file.
This combination 'shows' all formulae. When I opened it up next time it still
showed all the formulae. Pressing this combo might help.
Steve Thompson
"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks

Pleased you're sorted Roger. Elisabeth, thanks for info re F2. I'll bear that
in mind in future :)
Steve Thompson

"Elisabeth D" wrote:

F2 allows you to edit the contents of the cell and in this case it also
"refreshes" the cell to conform to the new formatting. It seems like
something that should automatically happen without the F2 intervention, and
yet...just one of the quirks we have to live with I guess.

Glad it worked.

Elisabeth

"Roger Thompson" wrote:

Well done Elisabeth, your advice has solved the problem completely. I had
tried reformatting the cells to General but had not pressed F2, what does
pressing F2 actually do?

"Elisabeth D" wrote:

The cell may be formatted as text. Try changing it to General and then
select the cell and press F2 and then ENTER.

Elisabeth

"Roger Thompson" wrote:

Thanks for the replies, have tried both suggestions and they do replicate the
problem across the whole worksheet, but the problem I have is trying to edit
the cell location of the data source for individual cells, as i say if I try
and edit the data so that the source location is different E.g. changing data
source from =data!A100 to =data!A150 then the location (=data!A150) appears
rather than the data in that location.

"jinkysteve" wrote:

Roger, I once had a similar problem which turned out to simply be that I had
pressed Ctrl key and the key to the left of number 1 before saving my file.
This combination 'shows' all formulae. When I opened it up next time it still
showed all the formulae. Pressing this combo might help.
Steve Thompson
"Roger Thompson" wrote:

Hi,

I am using a worksheet that loads data referenced to another separate
worksheet, ie contents of cell equals data inputted to location in other
worksheet. I am now finding that instead of the data appearing in the cell
(e.g. 1234) I am getting the location of the data appearing (e.g. =data!A100
) I am sure that this is just a problem with cell formatting but can someone
point me in the right direction please?

Thanks