Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Trigger (2002)

    I have 3 spread sheets - 1 master and 2 related (Company 1 & Company 2). I'd like to set up a way to take selected values and place them into either one of the 2 related - depending on the company name entered and a trigger "Yes" value.
    eg Master 1 has a number of columns including
    A1= Record, B1 = names, C1 = Company, D1 = Insurance (D1 is a Yes/No value), E1 = Details
    The logic is like, if D2 = YES & C2 = Company 1(Name), put the value of name, the Record#, etc into the next blank cells in Company 1spread sheet. If Company 2 put the value of name and the Record#, etc into the next blank cells in Company 2 spread sheet. I don't necessarily need the related spread sheets to open.
    I hope this makes sence!!
    Any lounger got any ideas?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Trigger (2002)

    Form the data layout you describe, it seems that pivot tables will offer a simple alternative.

    The downside is that you will need to refresh the data each time you add new items, however there will then be no need for a macro to paste the values to the individual spreadsheets. (there will alos be no need to code a macro to maintain/dlete changes ofnteh spreadsheets when data is amended/dleted in the master)

    If you are unfamiliar with pivot tables and can't make sense of the help text, post back for more advice.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Trigger (2002)

    Kieran, Thanks for your response.

    I was hoping that there may be some type of formula that I could plug into the trigger cell as the related spread sheets have a lot of other fields. I have a basic understanding of pivot tables but not sure how it could be applied in this case. I'd be interested to know how if you have any suggestions.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cell Trigger (2002)

    Some suggestions:
    Why not create just the master file? You could add an autofilter and choose the company of interest and you will then have the subset of just that company. You can also use subtotal to get info on this "filtered set". Pivot tables could be created from this master dataset to summarize the data.

    If you must keep individual files, I would not try to keep an up-to-date master. The best way would probably be to create the master "on the fly" whenever it is needed, by looping thru all the appropriate workbooks, opening them, reading the data, combining all of them, sorting as needed, then putting it into a newly created "temporary master".

    It will be very difficult to keep multiple files in sync, so this gets around it by not requiring the master to be available except when "needed" and then it is created.

    I do not think this is really practical (not sure how feasible it even is) using formulas.

    Steve

Posting Permissions

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