Results 1 to 4 of 4

Thread: [RESOLVED] Creating views of certain data from a master worksheet

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Resolved [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,

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width