Understanding the formula

[ExcelRoy]

Hi,

I have the following formula I am trying to understand

=IF(IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0)))=0,"",IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0))))

the $DU$16:$DU$512 part is what is being returned, but I am struggling to understand the $EC$16:EC16 part ?

Many thanks for any help

 

[Panda XXIV]

Hi,

I have the following formula I am trying to understand

=IF(IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0)))=0,"",IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0))))

the $DU$16:$DU$512 part is what is being returned, but I am struggling to understand the $EC$16:EC16 part ?

Many thanks for any help

i dunno, I'm assuming Its the reference cell you wanted to compare with EO14? it can be written simply as EC16 or $EC$16 in that formula's case tho...

 

[ExcelRoy]

Ok thanks Panda,

Could this formula be modified to only return the $DU$16:$DU$512 part of the formula if CA16="ST"

Thanks

 

[Matty]

Hi,

I have the following formula I am trying to understand

=IF(IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0)))=0,"",IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0))))

the $DU$16:$DU$512 part is what is being returned, but I am struggling to understand the $EC$16:EC16 part ?

Many thanks for any help

Hi,

The following:

ROWS($EC$16:EC16)

Will return an incremental count as the formula is copied down, i.e. 1, 2, 3, 4, 5, etc.

Matty

 

[ExcelRoy]

Ah ok, thanks Matty,

so effectively the ROWS($EC$16:EC16) could be any other row?

Could this formula be modified to only return the $DU$16:$DU$512 part of the formula if CA16="ST"

Thanks Again

 

[Matty]

Ah ok, thanks Matty,

so effectively the ROWS($EC$16:EC16) could be any other row?

Could this formula be modified to only return the $DU$16:$DU$512 part of the formula if CA16="ST"

Thanks Again

In short, yes. But it would be best if you could share a small example of your data so that we can see what you're trying to do.

Matty

 

[ExcelRoy]

Hi,

Upon reflection of my request it should be

$CA$16:$CA$512 ="ST" and $CD$16:$CD$512 ="ST"

Thanks

 

[ExcelRoy]

Hi Matty,

EO14 = =MAX($EO$16:$EO$512)
EO16 = =SUM($EO15,OR($EM16=$EO$6,$EM16=$EO$7,$EM16=$EO$8)) (Dragged down to EO512)

EP16 = =IF(IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0)))=0,"",IF(ROWS($EC$16:EC16)>$EO$14,"",INDEX($DU$16:$DU$512,MATCH(ROWS($EC$16:EC16),$EO$16:$EO$512,0))))

EC16 = =IFERROR(IF('Parts Requisition'!B16="","",IF(D16="","",D16)),"")
DU16 = =IFERROR(IF('Parts Requisition'!B16="","",IF($X$4="","",$X$4)),"")

Basically the columns DU-EN contain parts details as a complete order
EP-FF I need to have only the outstanding parts listed which I already have working fine

The only change I would like now is if CA16:CA16 has "ST" in it then it not to be considered an outstanding part

Can I send you the sheet I am working on?

Thanks

 

[Matty]

Can I send you the sheet I am working on?

Thanks

Sure. Can you share it via an online file hosting site and I'll have a look. Alternatively, paste a few illustrative cells here.

Matty

 

[ExcelRoy]

Hi Matty,

https://www.dropbox.com/s/nzcj13hn5grxthw/test.xlsm?dl=0

Thanks