Inventory Spreadsheet, look up date and qty and sum (attachment)

[OilAndGasMan1984]

I'm trying to populate a Raw Material inventory spreadsheet by looking up product code and the date.

Have an issue with the formula currently in place. It's not working for future dates.. and returning either 0 or #ref. (I don't want to show #N/A)

=IF(ISNA(INDEX('RM OnHand'!$E$2:$E$1048576,MATCH($D17,'RM OnHand'!$C$2:$C$1048576,0),MATCH(F$8,'RM OnHand'!$D$2:$D$1048576,0))),0,INDEX('RM OnHand'!$E$2:$E$1048576,MATCH($D17,'RM OnHand'!$C$2:$C$1048576,0),MATCH(F$8,'RM OnHand'!$D$2:$D$1048576,0)))

[loginjmor]

Hi -

The example sheet you provided only has dates on February 2, 2017. So, when you are searching for Today() in Cell C3, you won't get any results. Likewise, since the only dates in the RM OnHand list are 2/2/17, future dates won't return anything either.

If I change the date in Cell C3 to 2/2/17, then I get a result of 4000.66 in Cell F18. Is that what you're looking for?

INDEX/MATCH will only bring the first instance back from a list. So if you have multiple instances of say R-ADB-VA0-000-00 on the same date, you will only get the first one of those back. Your formula won't accumulate the total. Maybe that's OK. I don't know.

As an aside - Instead of using IF(ISNA(formula),0,formula) use IFERROR(formula,0). It's a little simpler and easier to debug since you don't have the same formula in there twice.

Hope this helps.

[OilAndGasMan1984]

Thanks,

Yes- it does help. Thank you.

I've begun changing the If(isna formulas to Iferror..

The real hard part is the Backlog... I can't seem to write the correct formula for cell F20. I've attached a fresh file. Would you mind advising me please?

Thank you!

[OilAndGasMan1984]

I'm trying to get Cell Q385 to give me 6,647... Is my formula a bust?

=IFERROR(INDEX('RM Backlog'!$G$2:$G$1048576,MATCH($D382&Q$8,'RM Backlog'!$F$2:$F$1048576&'RM Backlog'!$E$2:$E$1048576,0)),0)