First time posting, so be gentle.
I have found myself in the midst of massive excel project with so many
constraints placed on me that I have reached my wits end.
I can not use VBA in the project. It must be
accomplished strictly using formula and standard functions from Excel 2007.
I can not change the layout. I must
use the poorly designed sheet as is.
It is possible I may be able to blow the whole thing up and do it right at
some point, but I can't do that now.
I have resolved most of the issues through endless searching on this forum
and others and through countless hours of beating my head into a brick wall.
Here is my dilemma.
data sheet there is a column that contains text that I would best describe as a formula concept. You can think of this as
text of a formula using named ranges. So something like =(Data1/8)+(Data2/16)+(Data3). Realize that there may be as may as
15,000 records. Most of them will be blank indicating that special conditions do not apply. When there is text information
there it indicates that special conditions do apply and what calculations are needed to be applied. Each formula may be
unique and not applied to another record. Also records will be added on a regular basis.
Further complication is
that the named ranges are limited to the scope of each of 1 of 6 calculation sheets. Each of the 6 sheets has a cell defined
as data1, data2, data3 etc. that is limited to the scope of the sheet. Each of the 6 sheets may have different value of data
I actually managed to use the EVALUATE function in a named range to get the formula pulled to the 6 sheets
and the formula resolved to the correct amounts. However when the value of the named ranged data1 changes, it does not update
the value of the named range including the EVALUATE function.
I tried recalculating the sheet but that did not
work. I could not get it to update the value other than by closing and reopening the sheet.
I have attached a
simplified example of what I am working with. Please realize the sheet2 is actually repeated 6 times and each different sheet
2 has a ranged named data1 restricted to that sheet and that may be of different values.
I can change the naming
conventions in whole or part, but I can not use VBA or change the page layout.
Thanks in advance for any ideas on