Extracting Dates

R

Reefaman

I have 13 worksheets, 1 for each month and a year summary.
Each worksheet uses column A to list each indiovidual day eg. 1st Jan,
2nd Jan etc. Column O is to indicate holidays taken so this will only
be blank or have a 1 in it. What I want to do is on the summary sheet I
would like to be able to list the dates that have a 1 in column O on all
the other worksheets. Is this possible and if so how?
 
F

Flintstone

Hello JimR

Because you’ve given no explanation for the location and relationship
between columns I can only guess at what your data might look like.
However, this might help you out.

=SUM(INDIRECT("A5:"&LEFT(ADDRESS(1,DAY(TODAY()),4),IF(DAY(TODAY())<27,1,2))&65536))

Paste this formula anywhere above row 5, will sum columns per day for a
month.

Matt
 
F

Flintstone

Hello Reefaman

Assuming your dates are listed starting in row 5, use either of these
formulas which ever best suites your needs. Paste either of these
formulas in cell O5 on the summary sheet and copy down to match the A
column. The first formula will return a blank cell or a 1 from Sheet2
cell O5. The second formula will sum cell O5 from Sheet2 through
Sheet13, totaling all the 1’s, and will return a blank cell if
nothing is found.

=IF(Sheet2!O5="","",Sheet2!O5)

=IF(SUM(Sheet2:Sheet13!O5)=0,"",SUM(Sheet2:Sheet13!O5))

I hope this helps.

Matt
 
R

Reefaman

Thanks for the reply Flintstone, unfortunately its not quite what I
wanted but after reading back my post I understand why you suggested
the solution. I will try to explain better.
If O5 = 1 on the January sheet I would like to use the date located in
A5, in this case 3/1/06 and put it in A1 on the Year Summary sheet
Then if O12 = 1 on the February sheet use the date in A12, 7/2/06, and
put it in A2 on the Year Summary sheet.
The worksheets would have be checked for 1's in column O on all
worksheets (January-December) but I only want to populate column A on
the Year Summary sheet with the dates that have a 1 posted in O.
Hope this explains things a bit better.
Tony
 
C

CLR

Hi Reefaman..........
I don't think you're going to get what you want as a Worksheet Function. It
will take a macro.....one that will select each of your sheets in turn, and
execute the AutoFilter on each according to the 1 in column O and then copy
the column A dates from those Autofilter results to the bottom of the list on
sheet 1, and so on through all the sheets.

I suggest you re-structure your request, asking for a macro solution, and
re-post it in the Microsoft.Public.Excel.Programming group.

Vaya con Dios,
Chuck, CABGx3
 
S

Scoops

Hi Reefaman

You can do it with formulas but it's ugly and I too think you'd be
better off with a macro, however:

Name the used range in column O as [month]Blanks (e.g. JanBlanks).

Name the same length range in column P as [month]NoBlanks (e.g.
JanNoBlanks)

Enter this array formula (press Ctrl+Shift+Enter) into the first cell
in the range NoBlanks:

=IF(ROW()-ROW(JanNoBlanks)+1>ROWS(JanBlanks)-
COUNTBLANK(JanBlanks),"",INDIRECT(ADDRESS(SMALL(
(IF(JanBlanks<>"",ROW(JanBlanks),ROW()+ROWS(JanBlanks))),
ROW()-ROW(JanNoBlanks)+1),1,4)))

Copy the formula down the length of the NoBlanks range and you should
now have a contiguous list of dates equal to those marked with a 1 in
column O.

Rinse and Repeat for each of your month sheets, amending the named
ranges and the formula for each page.

On the Year sheet, cell A1, enter a formula referencing the results in
the P column of the January sheet (e.g. = JanSheet!P1) and copy it down
to A31; repeat for each month (i.e. A32:A60 for February... A366 =
reference to December 31) and name the range YearBlanks.

Name the range B1:B366 YearNoBlanks.

Enter the formula above in B1, amend the names in it, and copy it down
to B366.

Hide column A and you should have the desired result.

I take no credit for the formula as I got it from Chip Pearson's
excellent site - http://www.cpearson.com/excel/noblanks.htm -

Hope it works for you.

I still think it would be neater to use some vba though.
 
R

Reefaman

Thanks for the replies. I will try the formula suggestion and let you
know how I get on. If it all gets to messy I will try going the vba
route.
Thanks again
 
Top