Conditional Cell Reference / Multiple options

**Spawnofsanta** · 06-13-2012, 08:06 AM

Hi all,

I am having fun with Excel and making it do wonderful things, but now trying to make my life simpler.

I have a strong nested IF going on allowing a single table to provide 4 reports via ActiveX option buttons.
*ActiveX is purely to clean up the table between each option because the table is normally locked down*

The issue:
The formula is a nightmare to update with any changes. E,g,Example of Cell G11 for the 2011 sales in Cyprus...

=IF($J$1=1,SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$D:$D,$F11&" "&"Total",'2011 11JUN12'!$E:$E,$D$8)+SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$D:$D,$F11,'2011 11JUN12'!$E:$E,$D$8),IF($J$1=2,SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$C:$C,D11,'2011 11JUN12'!$D:$D,C11),IF($J$1=3,SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$C:$C,D11,'2011 11JUN12'!$E:$E,C11),SUMIF(VarietiesSold,$D$8&" "&"Total",'Varieties 11'!$F:$F))))

This formula works perfectly, but making any changes requires manually changing every cell in the table because it cannot be dragged without messing

up all the other parts in the formula.

The resolution?:
What i would like to do is single out the different conditions into seperate cells.
The problem and reason for asking here is, how do i include an indirect or similar function into the above so it recognises the cell/row?

e.g.

All cells of the table in column G =IF($J$1=1,J2,IF($J$1=2,K2,IF($J$1=3,L2,M2)))

J2 =SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$D:$D,$F11&" "&"Total",'2011 11JUN12'!$E:$E,$D$8)+SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$D:$D,$F11,'2011 11JUN12'!$E:$E,$D$8)

K2 =SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$C:$C,'Results Form'!D11,'2011 11JUN12'!$D:$D,C11)

L2 =SUMIFS('2011 11JUN12'!$F:$F,'2011 11JUN12'!$C:$C,'Results Form'!D11,'2011 11JUN12'!$E:$E,C11)

M2 =SUMIF(VarietiesSold,$D$8&" "&"Total",'Varieties 11'!$F:$F)

The C, D and F11 in the above needs to recognise what row the cell is asking for it so it does not just return the same result in all of them.

I hope that made sense. I have attached a dummy copy with unlocked and unhidden columns.
I think 2003 versions will have a problem if they do not have the compatability pack installed.

Regards,

Jason

**NBVC** · 06-13-2012, 08:31 AM

What is it that you are having to manually update each time?

**Spawnofsanta** · 06-13-2012, 09:19 AM

Hi NBVC,

The monthly update is to the other tabs and fairly straight forward. The manual updates I see as becoming a problem would occur if I wanted to add/remove a function to the 'Result Form' or make changes to the layout.

Any changes to the formula have to be done cell by cell. I cannot drag down/across to alter the rest as the references for the entire formula change. Some of the formula grabs information from a changing column (option 4, Monthly sales) and the rest grabs from a changing row (option 1,2,3). .

If I lock it all down with absolutes, the opposite has occurred a couple of times where I found the formula were now looking in the wrong place.

The full version of that file has over 20 countries and I have plans for a further update breaking Countries into Region.

Regards,

**NBVC** · 06-13-2012, 09:53 AM

Unfortunately I am not sure how to resolve your query. I don't think you can use those references to update your formulas on a row by row basis....

Perhaps somebody can develop a UDF that maybe able to intelligently "evaluate" the function replacing the row/column references as needed.... unfortunately I am not that advanced with VBA to do it at the moment.