Combining two formulas

[Trax]

I have the following formula, which checks is cell D6 is TRUE (using a check box) and carries out an offset function to return the appropriate value. The formula is:

=IF(Calculations!D$6,OFFSET('Spends'!E26,$B$5,0,1,1),"")

I now want to add a second check box in which determines where the offset function derives its value, using Calculations!F$13 as the TRUS/FALSE, and the OFFSET result being taken from 'Spends'!E4. If this check box is false, it reverts to the previous statement above. In each case, I need the "" at the end of the statement.

The construct of the statement in ots own right is simple enough, but when I try to combine in, I am having trouble!

=IF(Calculations!F$13,OFFSET('Spends'!E4,Calculations!$B$5,0,1,1),"")

Can anyone please help.

[Jonmo1]

Perhaps

=IF(Calculations!D$6,OFFSET('Spends'!E26,$B$5,0,1,1),IF(Calculations!F$13,OFFSET('Spends'!E4,Calculations!$B$5,0,1,1),""))

[Trax]

Jonmo1,

Thanks for the merging, however, I can not seem to get it to work. I think the problem comes from the first TRUE statement (D$6), this part decides if the figure from the offset range is included or not, the second TRUE/FALSE statement determines which table the data comes from (Actuals in month or cumulative).

Lee

[Trax]

I have amended the formula to choose the data set correctly using the following formula:

=IF(Calculations!$F$13,OFFSET('ISAF Spends'!E5,Calculations!$B$5,0,1,1),OFFSET('ISAF Spends'!E27,$B$5,0,1,1))

I am not able to include the part that IF Calculations!D6 is FALSE, then make cell blank.

Any suggestions?

[Jonmo1]

OK, you have four possible combinations of True/False between D6 and F13
What do you want to happen for each ?

D6=TRUE F13=TRUE : Then I want ??
D6=TRUE F13=FALSE : Then I want ??
D6=FALSE F13=TRUE : Then I want ??
D6=FALSE F13=FALSE : Then I want ??

[Trax]

To assist with this, I have uploaded a samlpe workbook.

What I want it to do is, for the figure in Jan, is dependent upon the check box for Jan being ticked. if check box above the month is ticked, then a figure is included, if not it is blank. In addition, if the checkbox 'Actuals/Cumulative' is checked, then the figure is derived from the table in ISAF Spends data range of E6:P19. If it is unchecked, it is derived from ISAF Spends E27:P40

Another problem is the scoll bar to the left determines the row that the data is being read from, although I could make some changes and omit this.

[Jonmo1]

Try

=IF(Calculations!D$6,OFFSET(IF(Calculations!F$13,'ISAF Spends'!E4,'ISAF Spends'!E26),$B$5,0,1,1),"")

[Trax]

Thanks Jonmo1,

I just need to look at how to get a scroll bar to use 2 different Cell Links dependent upon the checkbox.