3-D sheet copying/linking in excel

[ckaye]

I searched but didn't find this question asked previously, so I hope someone knows how to do this.

Let's say I have a workbook with sheet A & sheet B. Cell A1 on sheet B links to cell A1 on sheet A. Now, I want to create sheet C, and copy cell A1 from sheet B to sheet C, but now have cell A1 on sheet C reference cell A1 on sheet B. In other words, no matter how many sheets I create, I want the formula in cell A1 on all the new sheets to link to the same cell on the preceding sheet

Is this possible? Thanks.

 

[SkipVought]

ckaye,

I'm not sure that I understand the problem.

If Sheet2!A1 contains the formula
[tt]
=Sheet1!A1
[/tt]
and you COPY Sheet2!A1 and paste on Sheet3, the reference in Sheet3!A1 will be
[tt]
=Sheet1!A1
[/tt]


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[GlennUK]

Hi ckaye,

I think Skip misunderstood your request, in that you asked for the links to always point to the [bold]previous[/bold] sheet, and not the first sheet.

BUT, what Skip has pointed out is that copying the formula will point to Sheet1!A1 everytime which IS an answer, but let me explain why ( before you tell Skip it isn't what you want ) ...
you want cell A1 on sheet B to link to sheet A
you want cell A1 on sheet C to link to sheet B

BUT if sheet B has the result of the link from sheet A, then sheet C will have the same result whether it links to sheet B or sheet A.

Hope I've made things clearer, and not more muddled.

Cheers, Glenn.

 

[SkipVought]

ie

If

A = B

AND

B = C

The it follows that

A = C

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[ckaye]

I guess maybe I used a bad example; I understand what you're saying. But if I have a sheet that has links to multiple prior sheets, and I want to copy this sheet and extend it out for three or more sheets (let's assume each sheet is a given year's financial statement, relying on some data from the previous year), the I want the sheet links not to be absolute, but relative.

Does that make any more sense?

 

[SkipVought]

ck,

You must then enter the formula on each sheet explicitly.

Record a macro with relative refrences as long as it will be the same relative offset each time. The macro will want to use the same Sheet Name, so you'll need to do something special with the sheet references. Post you code after you've recorded it.


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[ckaye]

Uhhh... code? I'm no programmer here, I was hoping there was an easy way of doing this. I know Lotus does it easily, so I was looking to see if Excel can do it. If it requires coding or macros, I'm up the creek.

 

[SkipVought]

Record a macro with relative refrences...

Tools/Macro/Record New Macro.

One of the BEST tools in MS Excel!

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

OR.....

just do it manually each time you create a reference.

As one surgeon said to the other, "Suture self!"

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[clevelandjwb]

I think much of the response to this has been not very helpful. I looked up this problem this morning and was surprised to see such a recent thread at the top.

Basically, Excel can not do simply what Quattro pro has been able to do for 15 years with great ease. From what I see of other items, Lotus can do it too but I have no experience there. This lack manifests itself in two ways, first, the sheet can not be made relative in the same way that row or column can be made relative. All sheet names are absolute when copying a formula. The second thing is that it is difficult or impossible to have a true 3-D array that can be referenced with three simple subscripts.

The problem I was dealing with was a simple monthly budget where there are columns representing various categories of expense and the balance is brought forward from the previous month. A formula in 'November 2004'!B2 would be ='October 2004'!B5. This works for November but when the November sheet is copied to December, it still references October (rather than the desired November).

I solved my problem by copying November to about a year's worth of sheets and then using Edit and Replace to change 'October 2004'! to whatever was appropriate for the sheet in question. By selecting the whole sheet this goes quite quickly and in about 10 minutes I had what I needed. This only works easily when you are only referencing one sheet. If I referenced things from two months ago and three months ago all on the same sheet this would not work well at all. I imagine a macro could be written to do this but VBA is foreign to me.

It is unbelievable that Microsoft can't implement a simple straight forward 3D functionality. Quattro had it in 1990 and Fortran in 1960. It is not a new concept.

JB