Dynamically building formulas

  • Thread starter Thread starter Default
  • Start date Start date
D

Default

Hi
I have a workbook with a number of detail sheets and one
summary sheet. On the summary sheet I am using linked
formulas ("Paste Special|Paste Link") to get the data,
but the cell references are subject to change every time
the detail sheets are updated (number of rows change,
etc. This requires me to either redo the link formulas
manually or to build these formulas dynamically using
critical parameters (source sheet name, column, row,
etc). I can successfully build the formula, but cannot
seem to get it to execute (the formulas for the linked
cell and the one I build dynamically look exactly the
same, but the one displays the actual formula and the
other the result). What should I do?
Thanks



need to summarise data in a workbook, using linked cell
values from the various sheets containing the detail.
Individual sheets are subject to change ito number of
rows, etc, which requires me to have the
 
Look into using OFFSET COUNT and MATCH to define your ranges.

For example:

=SUM(OFFSET(A1,1,0,COUNT(A:A)))

With a "standard" data table with headings in row 1 and you data in
continuous rows would generate a range from A2 to the last row of your data
table.

=SUM(A2:OFFSET(A1,MATCH("date",A1:A50),0))

would look for a specific date and end the range at that point.

There are numerous ways of doing this depending on your specific needs and
data layout.

HTH

PC
 
Default said:
Hi Paul
I am quite happy with the formula itself - the real issue
is to get the formula to execute. As a for instance: I am
building the following formula by taking references from
other places in the summary sheet, using concatenate to
get to the following formula: "=Source1!$B$2". Since this
formula is the result of another function
("=Concatenate..."), it is displayed exactly as shown
above in the cell and does not execute the link. How do I
get it to execute the new (built) function?
Thanks
D
Click to expand...

You have built a text string, not a formula! You can use INDIRECT to convert
a text string into a cell or range reference for use in a formula. For
example:
=INDIRECT("Source1!$B$2")
or
=SUM(INDIRECT("A1:A3"))

If you need more help, post the 'formula' that you are wanting to use.
 
Thanks-a-mil, it worked!
-----Original Message-----


You have built a text string, not a formula! You can use INDIRECT to convert
a text string into a cell or range reference for use in a formula. For
example:
=INDIRECT("Source1!$B$2")
or
=SUM(INDIRECT("A1:A3"))

If you need more help, post the 'formula' that you are wanting to use.


.
Click to expand...
 
It worked, thanks!
-----Original Message-----


You have built a text string, not a formula! You can use INDIRECT to convert
a text string into a cell or range reference for use in a formula. For
example:
=INDIRECT("Source1!$B$2")
or
=SUM(INDIRECT("A1:A3"))

If you need more help, post the 'formula' that you are wanting to use.


.
Click to expand...
 
Back
Top