multiple IF (increaseing rows)

[sdoppke]

Hi everyone, im strugling with dragging a formula to the right and to have the rows increase (but the columns stay the same) Im using the $ to freeze the column, but when dragged to the right, all it does is copy the formual exactly the same way. Any ideas? Here is the formula:

=IF('Week 1 Chart'!$D2="-","0",'Week 1 Chart'!$D2)

so idealy when dragged to the right the next formula would read:

=IF('Week 1 Chart'!$D3="-","0",'Week 1 Chart'!$D3)

Thanks in advance for any help.

sd

 

[MrKowz]

Try:

=IF(INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:D2))="-","0",INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:D2)))

 

[sdoppke]

Try:

=IF(INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:D2))="-","0",INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:D2)))

That worked! thank you... but (hopefully this makes sense) every 2 cells are merged. Is there a way to make the formula increase by 1 row (as stated before) every other cell? so it works in the proper merged cells. Sorry i didnt say that before, i didnt even think about it.


Thanks for your help!

sd

 

[MrKowz]

Give this a shot (And thank you for the feedback ):

=IF(INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:C2)*2)="-","0",INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:C2)*2))

 

[sdoppke]

Give this a shot (And thank you for the feedback ):

=IF(INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:C2)*2)="-","0",INDEX('Week 1 Chart'!$D:$D,COLUMNS($C2:C2)*2))

MrKrows, again thank you, i dont know how you figue this stuff out! I looks like this is skipping every 2 cells on the "Week 1 Chart" ws. If i were to play with till i get it right, would I play with the $C2:C2)*2 portion?

sd

 

[MrKowz]

MrKrows, again thank you, i dont know how you figue this stuff out! I looks like this is skipping every 2 cells on the "Week 1 Chart" ws. If i were to play with till i get it right, would I play with the $C2:C2)*2 portion?

sd

Ahh, I think I see your requirements now (it is really hard to figure out without a visualization of your data).

Try:

=IF(INDEX('Week 1 Chart'!$D:$D,ROUNDUP(COLUMNS($C2:E2)*0.5,0))="-","0",INDEX('Week 1 Chart'!$D:$D,ROUNDUP(COLUMNS($C2:E2)*0.5,0)))

 

[sdoppke]

That was it, thank you very much! I will post a picture or the file next time to help.


sd

 

[MrKowz]

That was it, thank you very much! I will post a picture or the file next time to help.


sd

Glad we got it sorted out. Thank you for the feedback!