Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 7th 05, 03:09 AM
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


  #2   Report Post  
Old January 7th 05, 05:16 AM
Dave R.
 
Posts: n/a
Default

I don't think it's possible to have a calculation get a function name from
some other cell.. (?)

Indirect is how you would grab letters and numbers to be used in cell/range
references, but you probably already know that.

It might be easier if you tested what you were trying to do on a very small
range, one piece at a time. It would also make it easier to interpret what
you're trying to do for those reading this NG, if you explained it on that
scale. If you don't get anywhere with your current formula, try it on a very
very small scale and post it here and people will probably know very quickly
what you're trying to do.











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 02:49 PM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM
Indirect references in a linked formula Markshnier Excel Worksheet Functions 0 November 15th 04 04:36 AM
Indirect references in a linked formula Markshnier Excel Worksheet Functions 1 November 15th 04 03:49 AM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017