Formula Errors! Help please!

Hello there,

I'm doing a consolidation for a company. I have a formula that seems to work for one company, but not for another. Obviously im making a mistake, ive gone through it 4 or 5 times but just can't work out what it is. if anyone could help it'd be much appreciated.

Within the attached document, in the "Consolidation Schedule" tab, Column B should be dragging in data from the "UK P & L Stats" and "UK Balance sheet" tabs of the workbook. If you look at column C you can see that i've managed to get it working for the US company: I just cant' seem to replicate the formula to the UK company.

Essentially, column B should have the same formula as column C only using two different tabs to drag the data from (UK company as opposed to US company).

Any help would be much appreciated for this as my boss is starting to put the heat on me to finish this and this is the only thing that's missing! He hates (rightfully) hard coded cells and wants everyone formula driven.

When you open the file, click on "dont update" if it asks for this!

Cheers

Thanks again

Roger.

B8 on consolidation schedule looks like it's pulling the right data, which cells are you saying have the wrong info?

Hi guys, the revenue side B8-B14 is fine....however, cells B22-24 don't seem to work. The whole thing then falls apart from B30 to B43 and just reports the totally incorrect figures. They should literally be line to line the same as the ones in the "YTD" column of the "UK P & L" sheet.

From B54 to B91 i don't know what formula to put in place, but those cells should drag through the corresponding numbers from Column B in the "UK balance sheet accounts" tab, using INDEX and MATCH similar to the formulas in place in the Column C of the "Consolidation schedule" tab.

Sorry if it wasn't clear before, hope it is now. This is a complex worksheet I agree, causing me pain on a Monday! Thanks again for looking at it and any help.

Roger.

For B22:B24 your cell references were two too low. try this:

Formula:

=INDEX('UK P&L stat accounts'!$1:$1048576,MATCH('UK P&L stat accounts'!$A20,'UK P&L stat accounts'!$A:$A,0),MATCH('Consolidation schedule'!C$6,'UK P&L stat accounts'!$4:$4,0))

For B30 use this and drag to B36

Formula:

=INDEX('UK P&L stat accounts'!$1:$1048576,MATCH('UK P&L stat accounts'!$A28,'UK P&L stat accounts'!$A:$A,0),MATCH('Consolidation schedule'!C$6,'UK P&L stat accounts'!$4:$4,0))

B39:

Formula:

=INDEX('UK P&L stat accounts'!$1:$1048576,MATCH('UK P&L stat accounts'!$A37,'UK P&L stat accounts'!$A:$A,0),MATCH('Consolidation schedule'!C$6,'UK P&L stat accounts'!$4:$4,0))

B42 - click and drag to B43:

Formula:

=INDEX('UK P&L stat accounts'!$1:$1048576,MATCH('UK P&L stat accounts'!$A40,'UK P&L stat accounts'!$A:$A,0),MATCH('Consolidation schedule'!C$6,'UK P&L stat accounts'!$4:$4,0))

I'll have a look at the other next but this should get you moving. Thanks

I've done a quick pass and think it's working, there were a couple of issues like items on the consolidated tab which didn't have corresponding items on the balance sheet and items with spaces after them which were breaking the lookups.

Let me know if that works for you. Thanks

Excellent cheers, i see the error of my ways there. Thanks, appreciate this worbooks a bit of a beast.

Roger.