View Single Post
  #1   Report Post  
Damian
 
Posts: n/a
Default Relative Indirect Formula Referencing?

I have many types of data that need tracking, but I want a universal
log/report to distribute, so I created a workbook to accomplish this:

The workbook contains 13 worksheets. The first one is named "Settings".
The other twelve are named "MMMM YYYY" for the 12 months of the year. The
monthly worksheets reference the "Settings" worksheet for everything except
the data the users enter: the report headers, column names, summary labels,
and validation tables.

But is something Driving me crazy: Referencing Formulas!

===REFERENCING FORMULAS===
I have created "Master Formulas" area in cells A20:H24 with columns A~B
merged & C~H merged to create two fields. In the first I have the formula
names, which are relative to the column names the user chooses for their log.
In the second field I have formulas (sans the "=" so they are interpreted as
TEXT). For simplicity let's just focus on cell C20, which contains the first
formula.

I want to be able to use the formula for calculations on the monthly
worksheets for data in their respective rows. I cannot figure out how to
make Excel SEE my formula nor have I figured out how to make the cells
referencing the formula use their own address for the calculations (instead
of the address of the master formula cells).

I've tried using the INDIRECT function in tandem with the ADDRESS, ROW, and
COLUMN functions but to no avail. No matter what the formula is in cell
Settings!C20, I cannot come up with a formula in Column H of "January 2005"
that can grab the formula in C20, change the relative address values to match
its location, and use it to calculate/manipulate data in columns A~I.

The only pathetic thing I've been able to come up with all day has been
variants of the following:

Settings!C20 Equals:

"IF(ISNUMBER(ADDRESS(ROW(),COLUMN(),4,1)),"ADDRESS (ROW(),COLUMN(),4,1)*OFFSET(ADDRESS(ROW(),COLUMN() ,4,1),0,1))",(IF(ISBLANK(ADDRESS(ROW(),COLUMN(),4, 1)),"",(IF(ADDRESS(ROW(),COLUMN(),4,1)="ALL DAY",10,"?")))))"

January2005!H8 Equals:

CONCATENATE("=",Settings!C20)

I've seen a lot of geniuses on this board so I'm hoping someone has an idea
to make this work. Thanks!

Damian