Summarising data.

**Steve-B** · 06-16-2009, 09:12 AM

NBVC hows things....

See attached.

Essentially all I want is the summary sheet to show the data in where columns S, W, AA, AE, AI, AM have a 'red figure' (number above 0). Where this is the case I want the data in all the columns on the summary sheet to be filled with the data in each day's record sheet. (This also needs to work as a drop down!!!)

Many thanks as always!

**NBVC** · 06-16-2009, 09:42 AM

Originally Posted by Steve-B

NBVC hows things....

See attached.

Essentially all I want is the summary sheet to show the data in where columns S, W, AA, AE, AI, AM have a 'red figure' (number above 0). Where this is the case I want the data in all the columns on the summary sheet to be filled with the data in each day's record sheet. (This also needs to work as a drop down!!!)

Many thanks as always!

Originally, you said if the user enters something in column D... then show in summary table.. you also didn't specify multiple sheets being summarized....

Also, can't "filter" by font/background colour.. unless you want to get into VBA.. so is the criteria if there is a number in those columns S, W, AA, AE, AI, AM greater than 0, then include? Or if there is a name in Column D include, or what?

**Steve-B** · 06-16-2009, 09:52 AM

Apologies for adding.

In order to keep it simple, i only need data shown in the summary sheet where the figure in cell s,w,aa,ae etc are filled above 0. (Conditional formatting only used to show areas where stock is needed... don't need this as part of the formula.)

Many thanks.

**NBVC** · 06-16-2009, 10:53 AM

See attached for Formula solution:

In each Date sheet, add column AO formula in AO2, copied down to however far you want:

=IF(D2="","",SUM(S2,W2,AA2,AE2,AI2,AM2)>0)

Then in Summary Sheet, Formula in D2 to count number of TRUE in relevant sheet:

=COUNTIF(INDIRECT(C2&"!AO:AO"),TRUE)

Formula in A6:

=IF(ROWS($A$1:$A1)>$D$2,"",INDEX(INDIRECT($C$2&"!A1:A100"),SMALL(IF(INDIRECT($C$2&"!AO1:AO100")>0,ROW(INDIRECT($C$2&"!AO1:AO100"))),ROWS($A$1:$A1))))

adust ranges to suit your data in date sheets... then Confirm with CTRL+SHIFT+ENTER not just ENTER..

Copy down as far as you want.

Then Copy formula in A6 across all columns...

You will need to adjust the red range to match the columns in the date sheets, then confirm each with CSE keys and copy down.

**Steve-B** · 06-17-2009, 04:56 AM

Many thanks yet again for your help.

Genius...

**Steve-B** · 06-17-2009, 11:21 AM

BUMP!

PM sent to NBVC in order to change the INDIRECT formula so that the drop down list is changed from days of the week to dates what would I need to do.....

**NBVC** · 06-17-2009, 11:22 AM

From PM:

If I wanted to change the drop down list to dates rather than days of the week do I need to update the indirect formula?

So you will have a date, like June 17, 2009 and you want it to know that is a Wednesday and therefore look up the sheet named 'Wednesday', is that correct?

**Steve-B** · 06-17-2009, 11:35 AM

I actually now have to change the tab name and the drop down list to link as a date.

No days will be used a list of dates will be. A workbook will have all the days on the month and a summary sheet.

Hope that makes sense. Thanks for the reply btw!

Format of the date will be dd.mm.yy if you need to know.