Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks

It is a little hard to tell from your description but the one thing I would
want to know first is the numbers you are retrieving, are they populating
your spreadsheet with numbers or with text. You will be able to tell by
trying to format the cells (increase the number of decimals. If more decimals
show up you have a number, if not then you have text). Your formulas will
obviously not work if the values are text. This scenario would explain why
thing work after you enter and leve the formula bar because Excel will do an
automatic type conversion if the contents are numeric. Let me know. If this
is the case it is not too bad to fix...
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks

Unfortunately that is not the case. The information coming in is all numbers.
A funny thing just happened though, I was sick of it and I wanted to close
the file, but before closing it I decided to click on save. Once I clicked it
all the formulas all the way down to the final totals were calculated.

Is there any way that I could build this into the code some how, not meaning
the save command, but the actual on save, update information ?

Thanks for your help.

"Jim Thomlinson" wrote:

It is a little hard to tell from your description but the one thing I would
want to know first is the numbers you are retrieving, are they populating
your spreadsheet with numbers or with text. You will be able to tell by
trying to format the cells (increase the number of decimals. If more decimals
show up you have a number, if not then you have text). Your formulas will
obviously not work if the values are text. This scenario would explain why
thing work after you enter and leve the formula bar because Excel will do an
automatic type conversion if the contents are numeric. Let me know. If this
is the case it is not too bad to fix...
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks

Try using Ctrl + Alt + F9 to get a full calc (not just the dirty cells).
Check out this site for more info on calculation secrets. It may be that whe
your data is coming into the sheet the cell is not being flagged as dirty...
http://www.decisionmodels.com/calcsecrets.htm
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Unfortunately that is not the case. The information coming in is all numbers.
A funny thing just happened though, I was sick of it and I wanted to close
the file, but before closing it I decided to click on save. Once I clicked it
all the formulas all the way down to the final totals were calculated.

Is there any way that I could build this into the code some how, not meaning
the save command, but the actual on save, update information ?

Thanks for your help.

"Jim Thomlinson" wrote:

It is a little hard to tell from your description but the one thing I would
want to know first is the numbers you are retrieving, are they populating
your spreadsheet with numbers or with text. You will be able to tell by
trying to format the cells (increase the number of decimals. If more decimals
show up you have a number, if not then you have text). Your formulas will
obviously not work if the values are text. This scenario would explain why
thing work after you enter and leve the formula bar because Excel will do an
automatic type conversion if the contents are numeric. Let me know. If this
is the case it is not too bad to fix...
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks

Thanks for you help Jim

Unfortunately the Alt + Ctrl + F9 still does not work. I think it just has
to do with the fact that the spreadsheet is a monster and therefore for 1
number to change would actually mean quite a few calculations.

BTW. Thanks very much for the site it seems very good.

"Jim Thomlinson" wrote:

Try using Ctrl + Alt + F9 to get a full calc (not just the dirty cells).
Check out this site for more info on calculation secrets. It may be that whe
your data is coming into the sheet the cell is not being flagged as dirty...
http://www.decisionmodels.com/calcsecrets.htm
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Unfortunately that is not the case. The information coming in is all numbers.
A funny thing just happened though, I was sick of it and I wanted to close
the file, but before closing it I decided to click on save. Once I clicked it
all the formulas all the way down to the final totals were calculated.

Is there any way that I could build this into the code some how, not meaning
the save command, but the actual on save, update information ?

Thanks for your help.

"Jim Thomlinson" wrote:

It is a little hard to tell from your description but the one thing I would
want to know first is the numbers you are retrieving, are they populating
your spreadsheet with numbers or with text. You will be able to tell by
trying to format the cells (increase the number of decimals. If more decimals
show up you have a number, if not then you have text). Your formulas will
obviously not work if the values are text. This scenario would explain why
thing work after you enter and leve the formula bar because Excel will do an
automatic type conversion if the contents are numeric. Let me know. If this
is the case it is not too bad to fix...
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks

If you are over 65,536 dependancies then for each change everything (dirty or
not) has to re-calc and you will be waiting for a while. Where is your raw
data coming from?
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Thanks for you help Jim

Unfortunately the Alt + Ctrl + F9 still does not work. I think it just has
to do with the fact that the spreadsheet is a monster and therefore for 1
number to change would actually mean quite a few calculations.

BTW. Thanks very much for the site it seems very good.

"Jim Thomlinson" wrote:

Try using Ctrl + Alt + F9 to get a full calc (not just the dirty cells).
Check out this site for more info on calculation secrets. It may be that whe
your data is coming into the sheet the cell is not being flagged as dirty...
http://www.decisionmodels.com/calcsecrets.htm
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Unfortunately that is not the case. The information coming in is all numbers.
A funny thing just happened though, I was sick of it and I wanted to close
the file, but before closing it I decided to click on save. Once I clicked it
all the formulas all the way down to the final totals were calculated.

Is there any way that I could build this into the code some how, not meaning
the save command, but the actual on save, update information ?

Thanks for your help.

"Jim Thomlinson" wrote:

It is a little hard to tell from your description but the one thing I would
want to know first is the numbers you are retrieving, are they populating
your spreadsheet with numbers or with text. You will be able to tell by
trying to format the cells (increase the number of decimals. If more decimals
show up you have a number, if not then you have text). Your formulas will
obviously not work if the values are text. This scenario would explain why
thing work after you enter and leve the formula bar because Excel will do an
automatic type conversion if the contents are numeric. Let me know. If this
is the case it is not too bad to fix...
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks

It turns out that for even the simplelest change even in one of the input
cells it will not re-calculate unless I hit Ctrl- Alt- Shift- F9. When this
happens it still a long time for all the numbers to get recalculated. It must
just be the number of dependancies.

I will inform my users that they must either save the file, or hit the
aforementioned C-A-S-F9.

Thanks again for your help. I really appreciate it.

"Jim Thomlinson" wrote:

If you are over 65,536 dependancies then for each change everything (dirty or
not) has to re-calc and you will be waiting for a while. Where is your raw
data coming from?
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Thanks for you help Jim

Unfortunately the Alt + Ctrl + F9 still does not work. I think it just has
to do with the fact that the spreadsheet is a monster and therefore for 1
number to change would actually mean quite a few calculations.

BTW. Thanks very much for the site it seems very good.

"Jim Thomlinson" wrote:

Try using Ctrl + Alt + F9 to get a full calc (not just the dirty cells).
Check out this site for more info on calculation secrets. It may be that whe
your data is coming into the sheet the cell is not being flagged as dirty...
http://www.decisionmodels.com/calcsecrets.htm
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Unfortunately that is not the case. The information coming in is all numbers.
A funny thing just happened though, I was sick of it and I wanted to close
the file, but before closing it I decided to click on save. Once I clicked it
all the formulas all the way down to the final totals were calculated.

Is there any way that I could build this into the code some how, not meaning
the save command, but the actual on save, update information ?

Thanks for your help.

"Jim Thomlinson" wrote:

It is a little hard to tell from your description but the one thing I would
want to know first is the numbers you are retrieving, are they populating
your spreadsheet with numbers or with text. You will be able to tell by
trying to format the cells (increase the number of decimals. If more decimals
show up you have a number, if not then you have text). Your formulas will
obviously not work if the values are text. This scenario would explain why
thing work after you enter and leve the formula bar because Excel will do an
automatic type conversion if the contents are numeric. Let me know. If this
is the case it is not too bad to fix...
--
HTH...

Jim Thomlinson


"John Soarz" wrote:

Hello all,

I am hoping that you will be able to help me out because I am running out of
time and patience. Currently I have set up an excel spreadsheet that allows
users to use one main template and retrieve information from a back end
system into the template, then make adjustments to the current information
and from there upload the information back into the system.

The problem is that some of the formulas are not getting updated when some
numbers are changed. For some reason the only way that I can get the numbers
to update is when I go to the formula bar of that cell and then hit enter. I
have tried the F9 key to re-calculate, tried the calculate command using code
and have gotten nowhere. The files vary in size depening on how many Centres
each manager has. Usuually if a manager has 1-5 centres this is not an issue,
but for some reason when the number of sheets gets over that number I have
issues.

Any help would be greatly appreciated.

Thanks