If Today's date plus 2, then put data here.........

[avern]

Hi guys,

my first post ever! I am novice excel user and have this very complicated formula (for me anyways):

I have 2 files "Summary" and "Detail"

"Detail" has heading row of Mon-Fri with data below each date

In the "Summary", i also have a heading row of Mon-Fri.

I would like to pull the sum of all the data from "Detail" and insert it into "Summary" under the date heading that is 2 days ahead of today's date (not including weekends). I am ok with linking the data but need help in putting the data in the correct field.

Here is an example:

If todays date is Friday:

If "Detail" file shows:

mon tues wed thurs fri
1 4 3 2 2

Then "Summary" file should be:

mon tues wed thurs fri
12

I would like to have Mon, Wed-Fri stay blank instead of showing 0 as well.

Hope this is not too much to ask. Thanks for the help in advance!

 

[Peter_SSs]

the exact formula is as follows:

=IF(COUNT([Inventory.xls]Month!$G$51:$M$51),IF(COLUMNS($D37:H37)=MOD(MIN(WEEKDAY($H$2,2),5)+1,5)+1,SUM([Inventory.xls]Month!$G$51:$M$51),""),"")

Can you provide me with the proper formula in accordance with this one please? I tried following the same method but to no avail.

It is a bit difficult to be sure, since I suspect this formula is not in A3 and I don't really know what is in the 'Month' sheet of 'Inventory.xls', but try this in place of that formula.

=IF(COUNT([Inventory.xls]Month!$G$51:$M$51),IF(COLUMNS($D37:H37)=MOD(MIN(WEEKDAY($H$2,2),5)+1,5)+1,IF(SUM([Inventory.xls]Month!$G$51:$M$51),SUM([Inventory.xls]Month!$G$51:$M$51),""),""),"")


.. or possibly even this simple change to your existing formula

=IF(SUM([Inventory.xls]Month!$G$51:$M$51),IF(COLUMNS($D37:H37)=MOD(MIN(WEEKDAY($H$2,2),5)+1,5)+1,SUM([Inventory.xls]Month!$G$51:$M$51),""),"")

 

[avern]

Great! The simple formula worked out perfect. I will wait for the weekend to test out the weekend inventory # to make sure it shows up on Tuesday and will reply back if it is still a problem.

Thank you very very much for all your help!!!!

This message board is awesome! I would have never been able to do what I am doing in excel if it wasnt for this board.

I am member for life!