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!

 

[avern]

I'm not sure what you mean by 'work'.
Do you want it to show anything anywhere on weekends?

- If so, then it DOESN'T work but if you expalianed what you wanted to show, and where, on a weekend then perhaps I can adjust it to cope with that requirement.

- If not, then it DOES work - by not showing anything.

You are correct in that it does work by not showing anything. However what if i wanted to show it as Monday on weekends?

What sheet and cell is that formula in?

Does it correspond to any of the cells in my screen shot? If so, which cell?

Yes it corresponds to, for example, A3 if A3 was 0 i would like it to be blank.

 

[Peter_SSs]

However what if i wanted to show it as Monday on weekends?

Are you sure that is what you mean? Your original post said if the date was Friday, you wanted the result to show on Tuesday. But now you are saying if the date is Saturday or Sunday (that is, after Friday) you want the result to show on Monday (that is before Tuesday).

Yes it corresponds to, for example, A3 if A3 was 0 i would like it to be blank.

Depending on just what is in the InventorySyummary sheet, try one of these:
=IF([InventorySummary.xls]Containers!$G$6=0,"",[InventorySummary.xls]Containers!$G$6)
=IF([InventorySummary.xls]Containers!$G$6="","",[InventorySummary.xls]Containers!$G$6)


BTW, what version of Excel are you using?

 

[avern]

Are you sure that is what you mean? Your original post said if the date was Friday, you wanted the result to show on Tuesday. But now you are saying if the date is Saturday or Sunday (that is, after Friday) you want the result to show on Monday (that is before Tuesday).


Depending on just what is in the InventorySyummary sheet, try one of these:
=IF([InventorySummary.xls]Containers!$G$6=0,"",[InventorySummary.xls]Containers!$G$6)
=IF([InventorySummary.xls]Containers!$G$6="","",[InventorySummary.xls]Containers!$G$6)

Sorry you are correct I would like the result to show on Tuesday if the date is Saturday or Sunday.

BTW, what version of Excel are you using?

2003

 

[avern]

Are you sure that is what you mean? Your original post said if the date was Friday, you wanted the result to show on Tuesday. But now you are saying if the date is Saturday or Sunday (that is, after Friday) you want the result to show on Monday (that is before Tuesday).

Finally, how would I make this formula show as blank instead of zero?

 

[Peter_SSs]

I would like the result to show on Tuesday if the date is Saturday or Sunday.

Finally, how would I make this formula show as blank instead of zero?

With the same layout as post #6, and assuming a zero arises only if A3:E4 are all blank, try this in A9, copied across.

=IF(COUNT($A$3:$E$4),IF(COLUMNS($A9:A9)=MOD(MIN(WEEKDAY($G$1,2),5)+1,5)+1,SUM($A$3:$E$4),""),"")

 

[avern]

With the same layout as post #6, and assuming a zero arises only if A3:E4 are all blank, try this in A9, copied across.

=IF(COUNT($A$3:$E$4),IF(COLUMNS($A9:A9)=MOD(MIN(WEEKDAY($G$1,2),5)+1,5)+1,SUM($A$3:$E$4),""),"")

Thank you and I think it will work. However, a zero still shows up in A9. Is it because there are all zeros in A3:E4? If it is all zeros in A3:E4, then I would like the cell to be blank instead of zero.

 

[Peter_SSs]

However, a zero still shows up in A9. Is it because there are all zeros in A3:E4? If it is all zeros in A3:E4, then I would like the cell to be blank instead of zero.

Yes, it is because there are all 0s in A3:E4. Note the assumption in my last post ..

.. assuming a zero arises only if A3:E4 are all blank ..

The reason I made that assumption was because posts #9 to #12 were about getting rid of zeros in A3:E4. Now, apparently, they are back. We seem to be going round in circles here.

Are we having zeros in A3:E4 or not?

If we are,
- is it also possible that A3:E4 can also contain negative numbers?
- what formula did you end up with in A3 for example?

 

[avern]

Yes, it is because there are all 0s in A3:E4. Note the assumption in my last post ..The reason I made that assumption was because posts #9 to #12 were about getting rid of zeros in A3:E4. Now, apparently, they are back. We seem to be going round in circles here.

Are we having zeros in A3:E4 or not?

Yes there are zeros in A3:E4

If we are,
- is it also possible that A3:E4 can also contain negative numbers?

No

- what formula did you end up with in A3 for example?

=[inventory.xls]month!$F7

 

[Peter_SSs]

If you are manually inserting Quote tags, the opening tag should not contain a /. The / only goes in the closing tag. I have fixed that for you in the previous post.

=[inventory.xls]month!$F7

I will look at an alternative formula for A9, but could you not implement one of the suggestions from post #12 and use this in A3?

Assuming the 0 in A3 is because the referenced cell is empty ..

=IF([inventory.xls]month!$F7="","",[inventory.xls]month!$F7)

 

[avern]

I will look at an alternative formula for A9, but could you not implement one of the suggestions from post #12 and use this in A3?

Assuming the 0 in A3 is because the referenced cell is empty ..

=IF([inventory.xls]month!$F7="","",[inventory.xls]month!$F7)

Actually, to make things easier for me if you dont mind, 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.