Reporting within Excel

[Tappie]

Hi

I currently would like to carry out some reporting within
Excel.

I have information on a page and would like to search on a
date range (in essence for everyting in March) and copy
each row that has a March date (in a specific comlumn)onto
another sheet.

Is this possible ?

Thanks

Tappie

 

[Govind]

Hi,

Use Data- Filter - Advanced Filter and choose the data in the range of
March alone

regards

Govind.

 

[Guest]

The problem is that I would like to use it using vlookup
so that I can just change the month on the reporting page
and that page automatically updates as appropriate...

Is this possible ?

thanks

Tappie

 

[Govind]

Hi,

What is the layout of your original page(what columns etc) and what is
the layout of the reporting page ? If they both have a common column
then prob you can use that.

Otherwise , explain me in more detail your requirement.

regards

Govind.

 

[Tappie]

I have dates in some cells in column 'A' of sheet 1.

On sheet 2 of the spreadsheet I have set up a report to
report monthly statistics with information from sheet 1.

What I would like to do on sheet 2 for example is
type 'March' into a cell and then lift all of the
information from any rows that have a 'March' date in
column 'A' of sheet 1 and populate rows on sheet 2.

Is that any clearer (not to hot at explaining myself).

Thanks

Tappie

 

[Govind]

Hi,

This might not be possible with vlookup unless and until you have the
exact dates in March input in sheet 2. You can do a vlookup based on the
month in Sheet 2 , but that will retrieve only the first record which
matches the month of march in Sheet 1.

The ideal thing would be to use filters only.

If you just want a sum of all records the dates of which match March ,
then you can use sumif or sumproduct.

Regards

Govind

 

[Max]

Perhaps try this set-up which seems to work ..

In Sheet1
-------------
Assume the table is in cols A to C,
Dates in col A, other fields in cols B to C,
data from row2 down, viz.:

Date Field1 Field2
01-Mar-04 Data1 Data1
05-Mar-04 Data2 Data2
05-Apr-04 Data3 Data3
07-Apr-04 Data4 Data4
07-May-04 Data5 Data5
etc

Empty cols are assumed to the right of col C

Put in Q1: =COLUMN(A1)
Copy Q1 across to AB1

(Just a quick way of putting
the numbers: 1,2,3 ... 12 into Q1:AB1)

Put in D2: =IF($A2="","",IF(MONTH($A2)=Q$1,ROW(),""))

Copy D2 across to O2, then fill down by a safe max
number of rows in which data is expected in cols A to C,
say down to O1000.

In Sheet2
-------------
Let's reserve cell A1 for input of the month# desired
(i.e.: 1 = Jan, 2 = Feb, ... 12 = Dec)

Put the same col headers in A2:C2, viz:
"Date Field1 Field2"

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR($A$1+67)&":$"&CHAR($A$1+67)
),ROW(A1)),INDIRECT("Sheet1!$"&CHAR($A$1+67)&":$"&CHAR($A$1+67)),0)),"",OFFS
ET(Sheet1!$A$1,MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR($A$1+67)&":$"&CHAR($A$1+
67)),ROW(A1)),INDIRECT("Sheet1!$"&CHAR($A$1+67)&":$"&CHAR($A$1+67)),0)-1,COL
UMN(A1)-1))

Copy A3 across to C3, then fill down by as many rows
as was done in Sheet1's cols D to O, i.e. down to C1000

Cols A to C (in row3 down) will return
all the corresponding rows from Sheet1's cols A to C
according to the month# which is input in A1

For e.g., for the sample data-set above,
inputting in A1: 4 will retrieve and
display all the rows with April dates from Sheet1
in row2 down:

Date Field1 Field2
05-Apr-04 Data3 Data3
07-Apr-04 Data4 Data4

 

[Max]

Clarification:

The "header" range D1:O1 in Sheet1
is intentionally left empty in the set-up

But should there be any inputs made into this range in future
just make sure that the range doesn't contain any *numbers*
(text is okay)