Targeting different worksheets dependant on the date

W

WorkerB5

Any ideas how I do this?

Each month has exactly the same layout, all that changes are the type
of data within each one. I can perform the functions manually, but if
could get a peice of code to make Excel automatically do this it'd mak
my life easier :)

Thanks

WB
 
F

Frank Kabel

Hi
best you post your current code / your current functions and explain
exactly what you're trying to achieve
 
W

WorkerB5

The spreadsheet is at work atm, but I can remember enough.

The layout is

: DATE : NAME : REF : DATA : TYPE :

There is one row at the top with those as headers, then data of eac
type down the spreadsheet.

I have 12 worksheets for each months dispatch, and 1 worksheet whic
calculates the number of each type of transit each month, the tota
transits and then works them into percentages.

I've been asked to provide a breakdown for each week as well... this i
the problem part.

I need some code which will sit on a 5 week display and calculate ho
many of each transit went in each week. I can modify the formulae I'
using to calculate monthly to do it weekly, but I need something t
provide the data range, which in many cases will be on differen
worksheets and for some weeks will be on two worksheets...

Is it possible, or should I rework my worksheet?

Either way, I need to be able to display a running total for each wee
section.

WB
 
W

WorkerB5

I'm using the COUNTIF function to work out how many, but the range need
determining somehow.

WB
 
B

Bob Phillips

You can get the range with

OFFSET(A1,,,COUNTA(A:A))

something like

=COUNTIF(OFFSET(A1,,,COUNTA(A:A)),weeknum)

but why not just use the whole of the coilumn

=COUNTIF(A:A,weeknum)

(BTW not really sure how you will test, so my examples may need to be
adapted to your real situation).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top