Macro to create formulas in cells

**dcgrove** · 07-13-2011, 03:45 PM

Hello, I need a macro that will create several hundred formulas for me. in the attached workbook, you will find two sheets of interest. The sheet named Data has a very large data table with a little over 100 named ranges that are named with using sheetname&headervalue. So you will have names like "Data1.1.1". The sheet labeled Fran Calcs is where the magic needs to happen. In row three, I have written out example formula's for each column that need to be duplicated down the remaining 133 rows. The cells highlighted yellow contain the named ranges that applicable to those rows and columns. I would use an offset formula to refer to the named ranges, but they are all dynamic named ranges so INDIRECT fails. Without a macro to do this, I will be forced to write out just shy of 2000 formulas over 4 different sheets. Please think of the children!!!

I really do appreciate any help you may be able to provide.

Clayton Grove

**Leith Ross** · 07-13-2011, 07:28 PM

Hello Clayton,

It really does not make sense to use VBA to create these formulas. It would be easier to let VBA fill in the blanks for you. The reason is the code required to build the formulas is basically the same code to fill the cells in.

**dcgrove** · 07-14-2011, 08:46 AM

Leith, while I completely agree with you, I have to have the formulas becuase the workbook cannot contain any macro's. My end users can't alter their macro security due to Corporate IT processes. Also, the formula results are dynamic as the named range "franselect" refers to a data validation list that will be used to select a desired metric to be calculated. The macro I need is really just intended to be run once to prevent me from having to type out all 1900 formulas I will need. If you can see a way to do it without a macro and hours of typing, please let me know! I started the thread below to explore how I could refer to the named ranges with a cell reference to solve this very issue. But because my named ranges are dynamic, I discovered that I cannot use the INDIRECT formula to refer to dynamic named ranges.