Excel 2007 - referencing a cell value from another worksheet withi

[crisg]

Hi -
This appears to be a very simple process - but not matter what I do I cannot
get it work.

I have Sheet 1 through Sheet 4 of a workbook
Sheet 1 contains all values
Sheet 2-3 contain subsets of the values from Sheet 1
I would like for Sheet 2 and Sheet 3 to automatically update when the values
are updated in Sheet 1
All cells contain text only, not formulas
I have tried every combination possible and I cannot get the values in
Sheet2 - Sheet 3 to update automatically
Here is an example - In Sheet 3, Cell D3 I want to reference the value from
Sheet 1 Cell D46
I go to Sheet 3, Cell D3 and enter
Sheet1!D46 - does not work
Sheet1!D$46$ - does not work
= in front of the previous 2 references does not work
CELL(Sheet1!D46)
CELL(Sheet!D$46$)
I've used INDIRECT and countless other samples and no matter what I try -
all i can see in the Cell is the reference that I just typed in.
I know it's very easy and I'm missing some simple step - but I can't see it.
Thank you in advance for your assistance.

Regards,

 

[Khoshravan]

One possibility is that your workbook is in Manual phase rather than
Automatic. Press F9 to see if they update automatically.
To solve this, go to: Tools| Options, Calculation tab and under calculation
select Automatic.

 

[Alojz]

In sheet3, cell D3, do you type eqals sign at the beginning?
In options: don't u have checked: show formulas instead of results?

 

[crisg]

Hello Khoshravan -
Yes, the calculation is already set to Automatic and I already tried hitting
the f( manually as well. Is there a way for me to attach the worksheet to
this post so that you can see it for yourself?

Thanks,

 

[crisg]

Hello Alojz-
Yes, I did type the = sign in Sheet 3, D3. I also have it set to show the
results instead of the formula.
Is there a way for me to attach the worksheet to this post so that you can
see it for yourself?

Thanks,

 

[David Biddulph]

If you are seeing the formula rather than the result, there are two likely
causes:
You may have the cell formatted as text. Format as number or general, and
then re-enter the formula
or
You have the worksheet set to display formulae. In Excel 2003, that's under
tools/ Options/ View; you'll have to look up where it is in Excel 2007.

 

[Alojz]

hi,

o.k. if you like, send it to (e-mail address removed), will have a look tomorrow
around this time. May take some time till I answer, busy at work, looking
here just on evenings, now is close to midnight at place where I live.

 

[Alojz]

Crisg,

another thing came to my mind. I had the same experience with the workbook
which was not originally created in Excel but born as an export file (from
SAP in this case). I tried to create formula and for some reason it could not
return the value, even though the formula was o.k. I only saw the formula,
not it'result. Try to click on cell with formula, take it from input line to
the clipboard (whole formula, ctrl+c) and try to paste it to another cell in
ur worksheet (ctrl+v). Sometimes it works, I don't know why this happenes, if
it does not help, send the file to my e-mail.

Al