Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Advanced Basic Excel Project

  1. #1
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to have a worksheet to hold data. The fields would run from A:Q.
    Once data is entered in the initial worksheet, I would like certain fields from that data worksheet, to populate a second and a third worksheet. Those next two sheets would be formatted to produce a report and a pivot table.

    I can do much of the formatting for sheets 2 and 3, but do not have the ability to write the necessary code to fill the 2nd and 3rd sheets.

    The field headers in sheets 2/3 would mirror header in sheet 1.

    I tried to search previous posts to get some direction for this, but could not figure out a successful term to ge the search to work.

    I am reasonably confident this can be done, I'm just not experienced enough to do this.

    Any help is appreciated.
    K


  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi kojak43:
    Let us say you have an entry kojak in cell A1 of Worksheet Sheet1
    Now if you want to have the same entry in cell A1 of Worksheet Sheet2
    Then in cell a1 of Sheet2 you will enter

    =Sheet1!A1

    I hope I have understood you right and not trivialized you request.

    Please post back if this is what you intended ... otherwise explain a little further and let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    A few questions:

    - must you have sub sheets?
    * do advantages outweigh the disadvantages?

    - is it necessary to replicate the data?

    - have your considered scenarios, hiding columns, pivot tables, data filters ...?
    It may be easier to prepare the following from one main sheet of information
    * prepare summary reports
    * prepare custom reports

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    On the sheet you wish to enter your data, right click on the sheet name tab, select "view Code" and paste in this


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:Q2000")) Is Nothing Then
    Sheets("Sheet2").Range(Target.Address) = Target
    Sheets("Sheet3").Range(Target.Address) = Target
    End If
    End Sub


    From now on whenever you enter any data on this sheet it within the range "A2:Q1000" it will be automatically mirrored on sheet2 and 3


  5. #5
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your answers:
    Yogi Anand:
    I assumed I would have to something reasonably work intensive to accomplish what I wanted. And your response was what I thought I would have to use.
    However, both Dave's answers might provide a different way.
    Dave Patton:
    I am not sure I can answer your questions as I do not know what Summary Reports or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no joy.
    If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind leading the halt around here.)
    Dave Hawley:
    The reason I wanted to use different worksheets was I assumed I could format the subsequent worksheets into the two reports I wanted.
    Your code would work if I needed to use contiguous cells. Worksheet 2 might use A,B,C,F,H,& Q. Worksheet 3 might use A,B,C,D,E,G,I,K,J,L & M
    That jumping around cells leaves me, and my Excel talent, out in the cold.

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello!

    Was just wondering if the info. on sheets 2 and 3 is a filtered sub-set of the first sheet? If so, record a macro to filter the first sheet based on whatever criteria you need, and then copy and paste the filtered records to the other two sheets. Or create a macro that uses Advanced Filter to filter the records and then copy them to the other sheets as needed.

    HTH's or gets you started in the right direction!
    D.

  7. #7
    Board Regular kojak43's Avatar
    Join Date
    Feb 2002
    Posts
    270
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave Patton:
    I am not sure I can answer your questions as I do not know what Summary or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no help.
    If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind (me) leading the halt (her) around here.)


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •