Multi Sheet Referencing for Formulas

I'm guessing the target reference is changing when you copy the formula, try making the reference absolute -

='Sheet 2'!$G$10

Hi Dave

Thanks for the reply, but I already tried this and when I drag AO1 down to AO2, it then returns a value the same as that in A01.



Any other suggestions?

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Here you go!

Very abridged version - but gets the point across!

There are no formula in the workbook you posted, can you amend it to demonstrate your actual problem?

Sorry about that, it was late on Friday afternoon and my brain had clearly fizzled out!

Try this one.

Cheers

Hi Follychops,

Your formula seems to be working, or am I missing something?

Sheet2 column A is calculating percentage from sheet1 columns A and B. Sheet1 column C returns the percent values from sheet 2. I tried copying it down and as I say it seems to work!

Dave

Hi Dave

Maybe my original explanation of the problem wasn't totally clear.

The problem I am having is this:

Sheet 1 holds the data, but the formula to calculate the percentage actually sits in Sheet 2.
I then have a "formula" back in Sheet 1 that reads of the actual formula in Sheet 2 and returns the result, in you guessed it, Sheet 1.

The problem I have is the "formula" ie ='Sheet2'!A1 , once pulled down doesnt return a correct value.

I really just want to have one cell in Sheet 2 that is referenced in Sheet 1.

So for example:

If in Sheet 1 I put in: ='Sheet2'!A1 - I want to be able to drag that "formula" down as many cells as I like, and it calculates the correct amount, no matter how many rows down I go.

I hope that clears things up a bit!

Cheers

Follychops,

The workbook you posted does exactly that already! I can't recreate the problem you describe.

I've come up with the following formula as a guess to how to get around this issue, but it isn't working:

=SUMPRODUCT(--('Current Transactions'!$O$2:OFFSET('Current Transactions'!$O$16,-1,0)="DEPOT"),--(IF('Current Transactions'!$AR2:OFFSET('Current Transactions'!$AR$16,-1,0)="Not Applicable",('Current Transactions'!$AK2:OFFSET('Current Transactions'!$AK16-1,0-'Current Transactions'!$AN2:OFFSET('Current Transactions'!$AN16,-1,0)/ABS('Current Transactions'!$AN2:OFFSET('Current Transactions'!$AN16,-1,0))),('Current Transactions'!$AR2:OFFSET('Current Transactions'!$AR16,-1,0)-'Current Transactions'!$AN2:OFFSET('Current Transactions'!$AN16,-1,0))/ABS('Current Transactions'!$AN2:OFFSET('Current Transactions'!$AN16,-1,0))))))

In addition to this, I need it to ignore cells in column AK that have no data in them.

Any ideas?

Hi there

I've had another look at this, and the worksheet I have posted, doesn't really capture the actual complexity of the worksheet in reality, so I will work on getting a copy of that up shortly so it might make more sense.

In the meantime I can answer a couple of your questions.

1) The Calculation is set to Automatic

2) I can't lock the cells/columns as this is a moving spreadsheet that will expand and shrink with time, so if cells are protected, this causes problems.in adding/deleting rows.

Once you see the full spreadsheet, which I hope to get up shortly, this will probably make a lot more sense.

Cheers for your replies in the interm though!

I've revisited this and am going to take a new tack, based on some of your comments, so will call this "solved" and start a new thread so we can look at this from a fresh perspective.

Cheers for your comments.