just how do i do this???

Felafel

New Member
Joined
Sep 2, 2002
Messages
11
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!!!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 2, 2002
Messages
11
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 2, 2002
Messages
11

ADVERTISEMENT

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

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Well-known Member
Joined
Feb 16, 2002
Messages
761

ADVERTISEMENT

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

New Member
Joined
Sep 2, 2002
Messages
11
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Sep 2, 2002
Messages
11
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!!!
 

Forum statistics

Threads
1,148,170
Messages
5,745,169
Members
423,930
Latest member
Simple77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top