Nested IF formula to pick up data from changing range

[lgetach]

Hi,

i have a worksheet that will be constantly updated.

In C60:CH60 i have an if formula which looks at newly added data (in D100:DH100) and updates the data in C60:CH60 to the most recent data inputted in D100:DH100. If there is no new data, i have it returning the sum of data from a table found above in D50,D26,D14 the current formula looks like this:

=IF(D$100=0,SUM(D50,D26,D14),D$110)

The worksheet is constantly updated with new information. the new information is inserted into the D100:DH100 so that is where the most current data is found the data that is replaced by this range will then be found in D101:DH101 and the data from before that is in D102:DH102 and so on (older data will move down as new data is added in).

The formula i would like would do the above, but in the case that there is a 0 inputted into any cell D100:DH100, the cell would pick up the most up to date data. In this case, it would look at D101 if there is a 0 in that cell, it would pick up D102, until it finds a number, if it doesnt, it would pick up the sum of D50,D26,D14. can this be done with a nested If formula?

if this seems outrageous, is there a better, easier way of getting this done? i am open to and welcome all suggestions. I hope someone can help me.

Thanks in advance.

 

[ace19852]

Hi and welcome to the board and I agree there must be a better way but until then try this in C60 to see if it does what I think you are after. If so you will need to copy to the desired range. I was a bit foggy on why made the cell references absolute but you will need to sort that out b4 copying

=IF(D$100<>0,D$100,IF(D$101<>0,D$101,
IF(D$102<>0,D$102,
IF(D$103<>0,D$103,
IF(D$104<>0,D$104,
IF(D$105<>0,D$105,
IF(D$106<>0,D$106,
IF(D$107<>0,D$107,
IF(D$108<>0,D$108,
IF(D$109<>0,D$109,
IF(D$110<>0,D$110,
SUM(D50,D26,D14)))))))))))))

Cheers

 

[lgetach]

I tried the above and I’m not quite there yet.


I should first state that I wrote my sample formula incorrectly it should be =IF(D$100=0,SUM(D50,D26,D14),D$101). Additionally, i am working with two rows of data being inserted. so cell C60 would just focus on what is inserted into D100, D102, D104,D106 and so on and C61 would focus on data inserted into D101,D103,D105,D107 etc.


I made the cell an absolute reference because as the new data is INSERTED into the cell, excel automatically updates the formula to move the formula down. so once new data is inserted, the formula automatically updates to =IF(D$102=0,SUM(D50,D26,D14),D$103)




I am looking of a way to override this. I would like for Excel to just look at the range that i have specified, D100:D110.


Thanks for the suggestion. I have modified your formula above to this:


=IF(SUM($D100:$D112)=0,SUM(D50,D26,D14),IF(D$100<>0,D$100,IF(D$102<>0,D$102,IF($D104<>0,$D104,IF($D106<>0,$D106,
IF($D108<>0,$D108,IF($D110<>0,$D110,IF($D112<>0,$D112))))))))


This formula would be perfect if there was a way of overriding the automatic update that changes the above formula to this below every time new data is inserted.


=IF(SUM($D102:$D114)=0,SUM(D50,D26,D14),IF(D$102<>0,D$102,IF(D$104<>0,D$104,IF($D106<>0,$D106,IF($D108<>0,$D108,
IF($D110<>0,$D110,IF($D112<>0,$D112,IF($D114<>0,$D114))))))))

 

[ace19852]

Try this assuming F1, G1, H1 etc are empty - if not choose 10 blank cells above the insertion of new rows

In F1
D100:D110

In G1
D100

In H1
D102

In C60

=IF(SUM(INDIRECT(F1))=0,SUM(D50,D26,D14),
IF(INDIRECT(G1)<>0,INDIRECT(G1),
IF(INDIRECT(H1)<>0,INDIRECT(H1),

etc then change the rest to suit

IF($D104<>0,$D104,IF($D106<>0,$D106,IF($D108<>0,$D108,IF($D110<>0,$D110,IF($D112<>0,$D112))))))))

Cheers