PDA

View Full Version : Solved: How Do I Write This Formula?



Cyberdude
12-11-2006, 04:36 AM
?XYZ16? is the defined name of a formula which looks like:
='C:\EXCEL Documents\Weekly Totals Rpt.xls'!ABC16
where ?ABC16? is a defined name for a formula in a different workbook. That formula looks like:
=Shares!$BQ$16

To get the value of the formula ?XYZ16? I write:
=XYZ16
This works just fine.

Actually ?XYZ16? is one of a family of formulas with a defined name for each. So I have a group of formulas with defined names like ?XYZ2?, ?XYZ10?, ?XYZ16?, and so on.

What I want to do is change the reference so that the ?16? is a variable value. Instead of writing:
=XYZ16
I?d like to write something like:
=INDIRECT(?XYZ? & 2 * ROW())
but I can?t seem to find the correct combination. Is this something that can?t be done?

Simon Lloyd
12-11-2006, 04:57 AM
Cyberdude, where would you get your variable from is it in a cell, sheet different workbook?

Regards,
Simon

Cyberdude
12-11-2006, 01:25 PM
Hi, Simon! Thanks for the reply.

The "variable" is actually a calculation:
XYZ & 2*(SelRow - 9) + 7
(ideally). What I'm doing is creating a formula in a macro which will insert it into a cell. "SelRow" is a variable in the macro which is given a value in the macro before the formula is inserted, so the example above might look like
XYZ & 2*(15 - 9) + 7
which reduces to
XYZ19
when it is inserted into the cell.

This is a technique I've used before elsewhere to construct ordinary variables, but I've never tried to construct a defined name on the fly. I'm afraid that may be a limitation.

Regards,
Sid