-
Feb 26th, 2012, 11:42 AM
#1
Thread Starter
Junior Member
[RESOLVED] Creating views of certain data from a master worksheet
Hey Guys,
Hope you're all well
I was wondering if anyone could offer some assistance with a particular task that I have been struggling with inb Excel 2003?
I have a spreadsheet with a 'master' worksheet from which I would like to extract certain data as 'views' of data onto other worksheets in the same spreadsheet following certain criteria also if data is updated on the master worksheet this should be reflected on all other worksheets displaying the same data.
I will try and explain using a simple example:
Master worksheet columns Ref, Project Name, Programme, Date Started , Forecast Completion, Baseline Completion, Status, Resource1, Resource 2
Worksheet 1- Show column Project Name, Resource 1, Resource 2 where (Status = Pipeline and Programme = A)
Worksheet 2 - Show Column Ref, Project Name, Resource 1, Resource 2 where forecast completion <= 31/03/2012 and status= Live sort by Programme.
The above is just an example, I have roughly 50 columns of data with need to be split out in to 12 worksheet with different criteria to make reporting easier.
Would also need any solution to be able to handle the insertion of new rows on the 'master' worksheet.
Hopefully that makes sense, any assistance would be greatly appreciated.
Thanks in advance,
-
Feb 26th, 2012, 02:58 PM
#2
Re: Creating views of certain data from a master worksheet
there could be several solutions for this, but possibly using sql queries may be simplest, see
http://support.microsoft.com/kb/257819
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Mar 2nd, 2012, 01:33 PM
#3
Thread Starter
Junior Member
Re: Creating views of certain data from a master worksheet
Hi and thanks for your reply,
I think SQL queries may work but can the results of the query be remain on a worksheet with a button to the refresh the results?
Would also need the new sheets conditionally formatted which is why I was thinking of using linked cells but then would loose the ability to query the data.
I have roughly 75 rows of data which need to be cut in different ways on different worksheets. Once done the data will only change if a new row is added to the master worksheet
Hope that makes sense?
Thanks in advance
-
Mar 3rd, 2012, 03:16 AM
#4
Re: Creating views of certain data from a master worksheet
as you want conditional formatting carried to the new sheet, possibly copy the whole sheet, then remove the unwanted columns and rows
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|