just how do i do this???

[Felafel]

Here we go....I'm really having trouble finding the words for this one...

I have a number of rows that have data corresponding to a day. For example Monday 21/08/02 $1000, Tuesday 22/08/02 $2000...etc etc

What i want to do is to have a value for each week and not see all the crap that comes in between.

At the moment the rows are being transferred from another spreadsheet, what i want to happen is when they come through i want them to be sorted into weeks starting from the beginning of the current financial year.

So instead of having 5 rows for all the data from Monday to Friday I just have one row for the week beginning (eg) 24/04/02.

This requires sorting the stuff into the date ranges and adding up all the other values.....and i just have no idea where to begin.....

Does this make sense????

Please Help!!!

 

[PaddyD]

Hi - how about this:

1) create a new field that stores the week data. For dates in col A, with a heading in A1:
=year(a2)&weeknum(a2)
2) Do a pivot table on the new field.

Paddy

 

[Felafel]

Because of the way this thing is i can't use a pivot table.....this has to be all automated for the sanity of the user....though mine is at stake.

.....and i think i need to also point out my knowledge is fairly limited!

I'm not even sure how to go about sorting the dates......

thanks for the input though......

 

[PaddyD]

Hi!

The combination of "my knowledge is fairly limited", and "has to be all automated" and "i can't use a pivot table" isn't going to make your life any easier (although I guess you know that already!).

Anyway, a couple of questions:

1) why can't you use a pivot table?
2) sorting by date gives the same order as sorting by 'weeks' (as long as you want to keep week 1 year 1& week 1 year 2 separate). Why not record a macro of you manually doing the sort, then assign the macro to a button on the sheet or something similar
3) Where is the data coming from. If you are pulling ot from another sheet, are you replicating all the data 'cos you need to, as an intermediate step for some other purpose etc?
4) How 'automated' is automated - what are you actually tring to do here?

More details please...

Paddy

 

[Felafel]

I know I know...but I'm a fast learner I promise

As per the requiremnets of my boss i can't use a pivot table, basically I'm creating this this that tracks and totals and subtotals and eveything else known to man, it works off a consultant timesheet and all he has to do is enter the path name and it all goes from there. Everything else is working it's now just down to this and then I'm finally finished....it's been a long road.

It's not so much that I want to sort the dates but put the individual days into a period of one week...that's more my problem not the actual sort. So basically I want to find every date that falls into a working week (say a week beginning 08/04/02...all values that fall within the next seven days or whatever....) After it's in this form I don't want to see the individual day rows....just figures for the whole week.

So i guess this is an intermediate kind of thing.....

Is this clear enough???

thanks again...

 

[PaddyD]

OK - have you used MS Query before? You could create a new query that looked at your source data in the other workbook, did some summary stuff & returned the results. (You would need to set up your data as a new data source first). You can also set the refresh frequencies etc to keep it 'automated'...

Paddy

 

[RichardS]

Using Paddy's idea of getting a week number, you could then sub-total whatever you need, based on the change in week number, then click on the minus buttons created by the subtotal process to hide the detail. Just a thought.

Richard

 

[Felafel]

so right about now is the first time i've ever heard of MS Query!

to tell you the truth, I'm really not sure what you're talking about!

there isn't some kind of magic VBA resource somewhere that'll fix all my problems is there?

yeah i guess what i'm getting at is i know more about vba than i do about anything else in excel so i'd rather approach it that way if it's possible.....just need a kick staert if you know what i mean

 

[PaddyD]

If you can code, surely the steps would be something along the lines of:

1) Identify your data range etc
2) Generate the relevant weeknum info
3) Perform the summary calculation.
4) Put the results somewhere

As always, there are any number of ways to get from (1) to (4). If you're not sure, you could consider recording a macro of the above alternatives & playing with the results to generalise. More than that, I'm not sure what to suggest (no VBA here I'm afraid!), although if you want help with the code (other than being pointed to examples from earlier posts - have you searched the site for clues?) you'll need to post back with lots more info on how your data's set up etc...

Paddy
This message was edited by PaddyD on 2002-09-17 18:38

 

[Felafel]

I don't want to sound annoying but.....actually i'm probably already there so we'll continue.....

it's more that i'm more confident with vba than other stuff...that doesn't mean i'm good at it...i understand the steps i've got to take i just don't know how to do it....i'm usually ok once i've had that initial push but starting from scratch terrifies me!

We need things simplified and in rudimentary english!!!