Replicating Formula

[excelit]

Heya!

Got a small issue...

Im using the following formula:

Please Login or Register  to view this content.

Ive tried replicating this..and I expected "E4" in the formula to change over to "F4" (if I replicate horizontally).

Strangely it didnt..and I dont want to be changing each formula myself.

Is there anyway of fixing this?

Just for an idea of why im using this: Ive got a spreadsheet who's sheet name changes each year, and the indirect formula simply updates that sheet name for me in the formula each time.

Thanks

[Marcol]

Try this

Don't put E4 in quotes

Please Login or Register  to view this content.

Hope this helps

[excelit]

Try this

Don't put E4 in quotes

Please Login or Register  to view this content.

Hope this helps

Replicates fine..but the formula doesnt work then.

The one I used shows me a value of "177" yet the one youve given just shows me 0.

[abousetta]

Marcol is right. I tried this and it works fine (E4 becomes E5 while the other constants (H1, I1) stay the same. You might want to look at the formula again or post a dummy workbook.

abousetta

[excelit]

Ok Attached Workbook.
PrSales_12 has the INDIRECT formula.

[Marcol]

1/. Are you trying to average the values on sheets "PrSales_10" & "PrSales_1011"?
2/. There is no value in "Settings!$F$6", and I can't see from the sheet what it might be.
Is it meant to be a percentage multiplier?

[excelit]

1/. Are you trying to average the values on sheets "PrSales_10" & "PrSales_1011"?
2/. There is no value in "Settings!$F$6", and I can't see from the sheet what it might be.
Is it meant to be a percentage multiplier?

Hi,
Yep..
And yes, F6 is a percentage multiplier..sorry I forgot to add that in.
I formatted it as a percentage, and then put 2% in that cell.

[Marcol]

Okay try this

Note that Your formula refers to Cells H1 & I1.
I have put the values in these cells from J1 & K1

In E4

Please Login or Register  to view this content.

Drag Across and then Down

If you need a whole number try

Please Login or Register  to view this content.

If all the sheets are in the one workbook, why not use in E4

Please Login or Register  to view this content.

Drag Across and then Down

Hope this helps

[excelit]

Okay try this

Note that Your formula refers to Cells H1 & I1.
I have put the values in these cells from J1 & K1

In E4

Please Login or Register  to view this content.

Drag Across and then Down

First one was perfect!
Thanks so much! REP Added.

[excelit]

UPDATE:
I replicated..and I keep on getting an error box saying:

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result,creating a circular reference

then it gives me 2 options

1) if you accidentally created the circular reference, click OK
2) To display the circular reference toolbar bla bla bla

Any idea?

[Marcol]

The Circular Reference is in your originally posted workbook.

1/. Ignore the warnings and go to Sheet "PrSales_12"

2/. Delete the old formula in E4

3/. Drag cell I1 to somewhere else, or just delete it, I can't see a use for it.

4/. Drag J2:K2 left to H2:I2

5/. Put the new formula in E4
Drag E4 across to P4 and then Down to Row 26.

Remember the percentage value in sheet "Settings" F6

Does that solve the problem?

[excelit]

The Circular Reference is in your originally posted workbook.

1/. Ignore the warnings and go to Sheet "PrSales_12"

?

3) I1? Thats like one of the main parts of the formula. It takes the value from PrSales_10 (which comes from H1, and PrSales_11 which comes from I1)

For some reason..(not having done any of your steps yet), the error isnt coming up now..

Quick note though..the formula youve given me is being carried out on 3000 rows (yes, 3000, lol) - im probably going to reduce that now to like 1000 as it keeps saying calculating formula.

[Marcol]

3) I1? Thats like one of the main parts of the formula. It takes the value from PrSales_10 (which comes from H1, and PrSales_11 which comes from I1)

For what purpose? Look at the changes I pointed out to you.

The formula you asked for is volatile therefore it will calculate every time there is any change in the sheet.

I would suggest you try the last formula I gave you, it isn't volatile and a hell of a lot simpler.

[excelit]

For what purpose? Look at the changes I pointed out to you.

The formula you asked for is volatile therefore it will calculate every time there is any change in the sheet.

I would suggest you try the last formula I gave you, it isn't volatile and a hell of a lot simpler.

Hi,

No, I cant use the last one because its linking to a sheet directly.
The only reason why im getting it to use H1 and I1 is because once cell G1 changes (currently says 2012), then H1 and I1 also change, causing the formula to change too, which is what I want.

H1 and I1 will increment every year (H1 now says PrSales_10, but next year itll say PrSales_11).
This way the OFFSET formula youve given me updates automatically.

[Marcol]

Okay you know what you want.
I can't see what more I can do to help