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]

Welcome to the MrExcek board!

See if this gets you started. I have done this on a single sheet, but hopefully you can adapt to two sheets if it seems to do what you want.

I have assumed that if the current day is Saturday or Sunday then the sum should not show at all in the Summary.

The formula I have used in the sample sheet below referes to cell G1. That was just for testing different days of the week and to show you where the result comes for the day shown there. In your actual sheet, you would use this formula in A8.

=IF(WEEKDAY(TODAY(),2)>5,"",IF(COLUMNS($A8:A8)=MOD(WEEKDAY(TODAY(),2)+1,5)+1,SUM($A$3:$E$3),""))

The A8 formula is copied across.

Excel Workbook
	A	B	C	D	E	F	G
1	Detail						Friday, 25 November 2011
2	mon	tues	wed	thurs	fri
3	1	4	3	2	2
4
5
6	Summary
7	mon	tues	wed	thurs	fri
8		12
Sum 2 days ahead

 

[avern]

Hi Peter and thank you for the introduction and the great response.....

It worked great but 2 issues that have surfaced:

1. what if the "Detail" file shows:
mon tues wed thurs fri
1 4 3 2 2
2 3 4 5 1

Basically, if it was 2 rows of data i needed to grab?

and...
2. i am using a conversion factor to convert it to a different number in a different cell in the file however i now get the #value error for the cells that have no value (zero). How can i make it so #value dissapears and the box remains blank?

 

[Peter_SSs]

Hi Peter and thank you for the introduction and the great response.....

It worked great but 2 issues that have surfaced:

1. what if the "Detail" file shows:
mon tues wed thurs fri
1 4 3 2 2
2 3 4 5 1

Basically, if it was 2 rows of data i needed to grab?

Would the Summary have two rows (12 and 15) or one row (27)?

2. i am using a conversion factor to convert it to a different number in a different cell in the file however i now get the #value error for the cells that have no value (zero). How can i make it so #value dissapears and the box remains blank?

This is a bit vague. Can you give examples, explain your layout etc. Perhaps stick to a single sheet layout like I had to start with? A small set of sample data and expected results and layout would help. My signature block has suggestions for how to do that.

 

[avern]

Would the Summary have two rows (12 and 15) or one row (27)?

One row (27)


This is a bit vague. Can you give examples, explain your layout etc. Perhaps stick to a single sheet layout like I had to start with? A small set of sample data and expected results and layout would help. My signature block has suggestions for how to do that.

summary file:
mon tues wed thur fri
27

(also in the file in another cell the 27 gets multiplied by 50):
mon tues wed thurs fri
1350

however, right now, the other dates with that multiplication formula referencing "mon", "wed-fri" show #value due to not having a value in the original "mon", "wed-fri" cells.

I hope this helps. Much appreciated your help.

 

[Peter_SSs]

Try something like this.

A9 and A14 copied across.
Remember to replace each $G$1 in the A9 formula with TODAY() as I described previously.

Excel Workbook
	A	B	C	D	E	F	G
1	Detail						Tuesday, 22 November 2011
2	mon	tues	wed	thurs	fri
3	1	4	3	2	2
4	2	3	4	5	1
5
6	Summary
7	Sum
8	mon	tues	wed	thurs	fri
9				27
10
11	Mult
12	50
13	mon	tues	wed	thurs	fri
14				1350
15
Sum 2 days ahead

 

[avern]

A14 works great but it looks like A9 is the exact same to the previous A8 formula:

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

 

[Peter_SSs]

A14 works great but it looks like A9 is the exact same to the previous A8 formula:

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

No, they aren't the same.

 

[avern]

Sorry about that. Must be going bug-eyed. I think everything looks fine but i guess i will have to wait until Monday to check it out as it is the weekend and i think the formula doesnt work on Saturdays and Sundays, correct?

Also, this question is tied into the original one as it is working with the same file:

what would i do to this formula to make all zero values show as blanks instead of zeros?

=[InventorySummary.xls]Containers!$G$6

 

[Peter_SSs]

... and i think the formula doesnt work on Saturdays and Sundays, correct?

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.

what would i do to this formula to make all zero values show as blanks instead of zeros?

=[InventorySummary.xls]Containers!$G$6

What sheet and cell is that formula in?

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