[SimonB]

I have an Excel workbook with multiple sheets, two of which comprise a three dimensional array of cells. Each of those two sheets has a similar pattern to other. Sheet1 figuratively overlays sheet2 such that cell H20 in sheet1 relates to cell H20 in sheet2, etc., accross the full sheet limits. Sheet1 is for user input and sheet2 contains some pretty complex formulas.

Both sheets are arranged in a large 20x20 grid, and each square in the grid is comprised in turn of a 3 x 3 grid of nine cells, making those two sheets each 60 columns by 60 rows. Each of the 9 cells in a sheet2 3x3 grid has a different formula, but the pattern of nine frmulas repeats itself throughout the sheet2 grid. Each of those formulas has references to the input values in sheet1 and to values in adjacent cells within sheet2.

The essence of all this is that each mini-3x3 grid of 9 cells has the same nine formulas in it, repeated throught the full grid of sheet2.

All those formulas use relative references, or references to named cells containing constants located elsewhere in the workbook.

What I want to do is build the formulas in only one location, somewhere else in the workbook, and not have to repeat them each time in the many squares where the calculations must be done. The copying and pasting of each 3x3 grid takes forever each time I modify the formulas, which I seem to be doing constantly. Is there a way to put something like a relative referance to a formula in the grid cells that says, "use the formula located in cell whatever, and have it behave as though it were located here"?

Thanks in advance for you patience in reading all this, and for any help you can provide.

SimonB

[The Villan]

Can you upload an example up here so that we can have a look at the animal (no sensitive info of course)

[SimonB]

Originally Posted by The Villan

Can you upload an example up here so that we can have a look at the animal (no sensitive info of course)

Villian, you are good to respond so quickly, thank you. Attached is the animal (an apt name). I have deleted three sheets in it to get the zipped file down within the prescribed limit of 500KB. Don't see any bad #REF!s as a result, but it could have corrupted something.

The two sheets to look at are LeachField-Input and LeachField-Calcs (sheets 1 and 2 respectively as described in my original posting.)

SimonB

[The Villan]

Give it another try Simon, as it doesn't seem that you have loaded the file

[The Villan]

Are you Boston USA or Boston Lincolnshire UK?

[SimonB]

Originally Posted by The Villan

Give it another try Simon, as it doesn't seem that you have loaded the file

I'm new at this...

[The Villan]

Quite a complex workbook (animal) :-)
OK had a quick look and the first question I would ask and want to understand is -

Why do you have to keep on changing the formulas? By constantly changing the formulas is a nightmare and can lead to errors in the spreadsheets unless you are doing an audit each time before you release the new uopdated version.

Can you give some examples of why and what you would change.

[SimonB]

Originally Posted by The Villan

Quite a complex workbook (animal) :-)
OK had a quick look and the first question I would ask and want to understand is -

Why do you have to keep on changing the formulas? By constantly changing the formulas is a nightmare and can lead to errors in the spreadsheets unless you are doing an audit each time before you release the new uopdated version.

Can you give some examples of why and what you would change.

I'm still developing it, so it's evolving. This is a question I have struggled with before, too, so I thought I would consult the experts and see how they would do it.

SimonB

[SimonB]

I'm in Boston, MA USA, not England, but I have a brother in Cornwall.

[The Villan]

You haven't explained why you would want to keep on changing the formulas. Until you answer that, then we are not moving forward.

[SimonB]

It is still being developed. As I move forward with the development I find the need to change the formulas, taking new variables into account, or correcing faulty logic, for example. Once I get it where I want it, I shouldn't have to change it any more.

[The Villan]

OK thats better. you had me worried there :-)

I can see where you are coming from.

I would keep it the way it is and do the following (as an idea)
Create a range name called FillTypeA for cells E15 to G17
Create a range name called FillTypeAll for cells E15 to AZ59

Then create a macro that will copy the cells in FilltypeA to all the other cells from E15 to AZ59

The macro which could be c alled CopyFillType would look like this.

Sub CopyFillType()
'
' CopyFillType Macro
' Macro recorded 05/06/2008 by Simon
'

'
Application.Goto Reference:="FillTypeA"
Selection.Copy
Application.Goto Reference:="FillTypeAll"
ActiveSheet.Paste
Application.Goto Reference:="R15C5"
End Sub

You can then make the changes in E15 to G17 and then run the macro to update all the other cells in FilTypeAll

I hope you understand what I am getting at.

Their are a lot of excellent Macro doods on here that can run you through this if you need any help. The fact that everything is relative makes it simple if you do as above.

Get back if that isn't quite what you were looking for - maybe a bit too simple :-)

I will be going to bed soon. 23:35 in the UK and i need to get up early tomorrow morning.

[SimonB]

To thoughts on this idea:
1) It's a good example of why I need to learn macros and/or VB. I Used to be really good at Lotus123 macros, but never picked up the MS stuff when the world shifted boats midstream.
2) I was wondering if there is some sort of function like Indirect that might provide a virtual translation of a formula in one cell to another.

Have a good night.

[The Villan]

I can't think of anything from my point of view that would simplify things the way you want, without a lot of effort and that then defeats the issue.

Personally, I would go down the macro route for what you are trying to do.