Ref. problem after sheet import
 

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

Ref. problem after sheet import
 

Hmmm, updates can be a bit tricky...

Search for "recalculate" in the Excel help. There's quite a bit of info in
there, but in short, this is what Excel says:

=========================

Press F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9
Calculates all formulas in all open workbooks, regardless of whether they
have changed since last time or not.

Press CTRL+SHIFT+ALT+F9
Rechecks dependent formulas, and then calculates all formulas in all open
workbooks, regardless of whether they have changed since last time or not.

=========================

So, try sending these keystrokes (using SendKeys) or use their VBA
equivalents (Calculate/CalculateFull). Also, make sure you take a closer look
on your preference settings, which can change the way how recalculations are
performed.

And although this is perhaps not applicable to you at this moment, please
keep in mind that there are separate ways to update links to external data
(see for example the UpdateLink method in VBA, and your application
preferences).

Cheers,
/MP

"Christian" wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

Ref. problem after sheet import
 

son wrote:
Hmmm, updates can be a bit tricky...

Search for "recalculate" in the Excel help. There's quite a bit of info in
there, but in short, this is what Excel says:

=========================

Press F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9
Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9
Calculates all formulas in all open workbooks, regardless of whether they
have changed since last time or not.

Press CTRL+SHIFT+ALT+F9
Rechecks dependent formulas, and then calculates all formulas in all open
workbooks, regardless of whether they have changed since last time or not.

=========================

So, try sending these keystrokes (using SendKeys) or use their VBA
equivalents (Calculate/CalculateFull). Also, make sure you take a closer look
on your preference settings, which can change the way how recalculations are
performed.

And although this is perhaps not applicable to you at this moment, please
keep in mind that there are separate ways to update links to external data
(see for example the UpdateLink method in VBA, and your application
preferences).

Cheers,
/MP

"Christian" wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.



Thanks for your promt reply.

Have tried Press CTRL+SHIFT+ALT+F9 and all other combinations
none work have also tried
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
no success
SendKeys after adding the sheet didn't work either with which ever
combination
update remote references is checked in the options and calculations are
automatic
So don't know what else to do unless to update them manualy with
a Worksheet_SelectionChange event.

Christian

Ref. problem after sheet import
 

Maybe you could group all the sheets and then
edit|Replace
what: = (equal sign)
with: =
replace all

and ungroup the sheets

Excel will see that you're reentering each formula and reevaluate it. Maybe
it'll be a one time thing if it wakes up excel.

Christian wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

--

Dave Peterson

Ref. problem after sheet import
 

Or maybe pratice some good ol' Voodoo... Black candles, chicken heads and all
that... :o)

Fact is, I've been forced to re-write cell formulae myself a few times in
the past. It's ugly, but when nothing else works and you really can't afford
to spend more time on making things work, then re-writing will definitely do
the trick...

But by all means, keep on serching for a proper solution though -- The truth
is out there! :o)

Cheers,
/MP

"Dave Peterson" wrote:

Maybe you could group all the sheets and then
edit|Replace
what: = (equal sign)
with: =
replace all

and ungroup the sheets

Excel will see that you're reentering each formula and reevaluate it. Maybe
it'll be a one time thing if it wakes up excel.

Christian wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

--

Dave Peterson

Ref. problem after sheet import
 

Changing = to = has never failed to update the formulas for me.

Has it failed for you?

Mat P:son wrote:

Or maybe pratice some good ol' Voodoo... Black candles, chicken heads and all
that... :o)

Fact is, I've been forced to re-write cell formulae myself a few times in
the past. It's ugly, but when nothing else works and you really can't afford
to spend more time on making things work, then re-writing will definitely do
the trick...

But by all means, keep on serching for a proper solution though -- The truth
is out there! :o)

Cheers,
/MP

"Dave Peterson" wrote:

Maybe you could group all the sheets and then
edit|Replace
what: = (equal sign)
with: =
replace all

and ungroup the sheets

Excel will see that you're reentering each formula and reevaluate it. Maybe
it'll be a one time thing if it wakes up excel.

Christian wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

--

Dave Peterson


--

Dave Peterson

Ref. problem after sheet import
 

Nope, as I said: re-writing the formulae in a sheet (or replacing = with =,
which is the same thing, basically) is the fallback solution that always seem
to do the trick.

However, it would obviously be nicer to always be able to rely on one of the
Calculate/CalculateFull/UpdateLinks/... methods (and F9 key-combinations),
but those seem to not always kick off the recalculations you'd expect (or,
rather: I'm not always clever enough to figure out exactly why they do not
force the recalculations I'd expect... :o)

So, if anyone out there has a fool-proof way of determining under exactly
which conditions each one of the calculation methods mentioned above will
fire off their respective recalculations (and when not!) then I'd be very
happy to learn about this...

Cheers,
/MP

"Dave Peterson" wrote:

Changing = to = has never failed to update the formulas for me.

Has it failed for you?

Mat P:son wrote:

Or maybe pratice some good ol' Voodoo... Black candles, chicken heads and all
that... :o)

Fact is, I've been forced to re-write cell formulae myself a few times in
the past. It's ugly, but when nothing else works and you really can't afford
to spend more time on making things work, then re-writing will definitely do
the trick...

But by all means, keep on serching for a proper solution though -- The truth
is out there! :o)

Cheers,
/MP

"Dave Peterson" wrote:

Maybe you could group all the sheets and then
edit|Replace
what: = (equal sign)
with: =
replace all

and ungroup the sheets

Excel will see that you're reentering each formula and reevaluate it. Maybe
it'll be a one time thing if it wakes up excel.

Christian wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

--

Dave Peterson


--

Dave Peterson

Ref. problem after sheet import
 

son wrote:

Nope, as I said: re-writing the formulae in a sheet (or replacing = with =,
which is the same thing, basically) is the fallback solution that always seem
to do the trick.

However, it would obviously be nicer to always be able to rely on one of the
Calculate/CalculateFull/UpdateLinks/... methods (and F9 key-combinations),
but those seem to not always kick off the recalculations you'd expect (or,
rather: I'm not always clever enough to figure out exactly why they do not
force the recalculations I'd expect... :o)

So, if anyone out there has a fool-proof way of determining under exactly
which conditions each one of the calculation methods mentioned above will
fire off their respective recalculations (and when not!) then I'd be very
happy to learn about this...

Cheers,
/MP

"Dave Peterson" wrote:

Changing = to = has never failed to update the formulas for me.

Has it failed for you?

Mat P:son wrote:

Or maybe pratice some good ol' Voodoo... Black candles, chicken heads and all
that... :o)

Fact is, I've been forced to re-write cell formulae myself a few times in
the past. It's ugly, but when nothing else works and you really can't afford
to spend more time on making things work, then re-writing will definitely do
the trick...

But by all means, keep on serching for a proper solution though -- The truth
is out there! :o)

Cheers,
/MP

"Dave Peterson" wrote:

Maybe you could group all the sheets and then
edit|Replace
what: = (equal sign)
with: =
replace all

and ungroup the sheets

Excel will see that you're reentering each formula and reevaluate it. Maybe
it'll be a one time thing if it wakes up excel.

Christian wrote:

The problem I am having is that after I import a Template sheet with
references
to the Master sheet the cell references are not updated.
I do this so when the master sheet is updated the same cell in the
other sheets
change to the same value.
For example:
The cell on the Imported sheet say A1 would have a reference
=Summary!$I$6
Summary is a Sheet in the workbook the sheets are imported to.
I have tried the SendKeys "{F2}~" after the code that imports the
sheet but that does not work. The only thing that works is if I select
the cell with the #Ref problem click in the formula bar and press
enter. like SendKey "{F2~}".
I don't want to go through all the cells like that as there could be
100 of them.
Is there another way to update the linked values?
Any help would be apreciated.

--

Dave Peterson


--

Dave Peterson


Thank Guys

There doesn't seem to be a profen way apart from replace = with =
To overcome this i ended up inserting the values in the imported sheet
with code as I go
and then inserted a Worksheet_Change event for the ranges that are
updated on the master sheet.
Thanks again for your input.
Much appreciated