Creating 6 summary sheets from 1 workbook, criteria based

[dirtyfrog]

Hi,

I've been asked to write a couple of sheets, and I'm , not sure how to even start!

A report is run every day, listing jobs our field engineers have been issued. The engineers are each assigned to a depot they work from. Three depots make up one cost centre (so for example, our Trent Valley cost centre consists of Derby, Mansfield and Nottingham depots).

So the first task - on selecting a cost centre, my sheet should retrieve all information for the 3 relevant depots. Continuing with the example above, I want to be able to select Trent Valley, and get all the job information from the Derby, Mansfield and Nottingham depots. (The sheet I am retrieving info from lists the data by depot, and not cost centre)

How do you go about copying either a whole row, or selected columns, between sheets? If at all possible, I don't want to have to use VBA because I've not tackled that yet and have no idea what I'm doing!

Another problem I anticipate - every time the report is run, it is saved with a different file name (i.e. 11_01_05.xls). As I'll be pulling information from this sheet, how do I get around constantly changing filenames? One possible solution, if it's possible - can I set the sheet to prompt me to enter a filename, or even browse using Explorer and select the appropriate file? Once I've told the sheet which file we are working from, could it then insert that filename in any formulae I've created?

Apologies that this is so vague, and that I'm basically asking for advice on how to get started with this sheet! It's something I've never done before, so I'm just stumped Any help would be appreciated!

 

[dirtyfrog]

Re: Creating 6 summary sheets from 1 workbook, criteria base

Any wee hints to get me started?!?

 

[howzat]

Re: Creating 6 summary sheets from 1 workbook, criteria base

Hi there,

Regarding updating references to external workbooks can be changes easily using edit > links, and then selecting the relevant file name accordingly. This is assuming the report is downloaded in the same format each time.

if you could post a small sample of the downloaded report, and then a sample of what you would like to end up with, then we can work out the mechanics from there.

 

[NdNoviceHlp]

Re: Creating 6 summary sheets from 1 workbook, criteria base

You will need VBA to accomplish your task. It is possible but requires some advanced coding. Before beginning, you should give careful thought to how your data is stored, how you want to manipulate the data, and how you want to present and store your output. It may be in your best interests to seek out a trained programmer to develop the code you need. Dave

 

[dirtyfrog]

Re: Creating 6 summary sheets from 1 workbook, criteria base

Yeah, after a lot of thought I've realised this task is beyond me at the minute, and certainly not possible without delving into VBA. We've got a nice easy manual copy n paste process to do what we need, and I've bought VBA for Dummies, so I'll start tinkering!