+ Reply to Thread
Results 1 to 7 of 7

Data from master worksheet auto filtered onto different worksheet

  1. #1
    Registered User
    Join Date
    07-12-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    3

    Data from master worksheet auto filtered onto different worksheet

    Hi,

    I am a self taught excel user (new to the forum) and am running short on a project I need to do. I am not very familiar with VBA but I don't think there is a way around for my problem. I've looked through plenty of forums and threads but couldn't really find what I wanted (I was unable to modify existing codes to solve my problem).

    Problem:

    From a frequently updated confidential list of suppliers that contains all supplier info (Sheet1 - I already know how to hide the worksheet with password), I need to show to users only the approved suppliers on an separate list that is automatically updated from the master list and non-modifiable yet sortable (Sheet2 - I'm guessing can be protected but with activated autofilters).

    I looked into auto conditional copying and auto conditional hiding but can't figure out which one would work better or work at all...

    Both lists are identically formatted and use the same headers. Row 1 to row 4 in Sheet1 have data for "validation lists". The data in the master list starts from row 9 to whichever last row that contains data. The supplier name is in column A, the status is in column B and the rest of the data is in the remaining columns (all need to be shown). The status column is the one that will have the "Approved" condition and it is automatically generated through a series of "if" formulas.

    Attached is the file I have for example.

    Any help is greatly appreciated, thanks

    Guillaume
    Attached Files Attached Files
    Last edited by gdutilh; 07-13-2010 at 12:14 PM.

  2. #2
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Data from master worksheet auto filtered onto different worksheet

    Hi Guillaume;

    I'm a believer in never copying data, so that there are 2 copies. Somehow they always get out of sync eventually, and it's a real bugger trying to figure out which copy is the correct one.

    I would do it this way: This puts the Row # (relative to A8) of all "Approved"s in Column X (or some hidden column)
    X8 : 0
    X9 : =X8+MATCH("Approved",INDIRECT(CELL("Address",OFFSET($B$9,X8,0))&":$B$65535"),0)
    And copy X9 formula down column X

    This is what X10 formula would be:
    X10 : =X9+MATCH("Approved",INDIRECT(CELL("Address",OFFSET($B$9,X9,0))&":$B$65535"),0)


    Then On Sheet2:
    A9 : =IF(ISNA(Sheet1!$X9),"",INDEX(Sheet1!$A$9:$V$18,Sheet1!$X9,COLUMN()))
    and copied down & across to V18 (on this worksheet)

    This is what V18 formula would be:
    V18 : =IF(ISNA(Sheet1!$X18),"",INDEX(Sheet1!$A$9:$V$18,Sheet1!$X18,COLUMN()))

    Just make sure that the address in red is the bottom right corner of the raw data.

    I'm attaching a file that demonstrates these formulas. Play around with Column B on Sheet1 and see the effect on Sheet2.

    BTW: The way I do it in my workbooks to make sure that $V$18 is set correctly so I never have to worry about adding or deleting rows:
    I set it to $V$19, and color cells $A$19:$V$19 black and change it's height to 4. Then I always insert or delete a row above the thin black row.
    Attached Files Attached Files
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Data from master worksheet auto filtered onto different worksheet

    I would take a simpler INDEX approach, giving an index number to each row with "approved" in the correct column.

    On Sheet1 in X9, put this formula and copy down as needed (this column can be hidden later):
    =IF(B9="Approved", X8+1, X8)

    On sheet2, this formula in B2 to indicate how many values should be found:
    =MAX(Sheet1!X:X)

    On Sheet2 you can pull over the matching values by putting this formula anywhere down in column A, then copying down and across as needed:
    =IF(ROW(A1)>$B$1, "", INDEX(Sheet1!A:A, MATCH(ROW(A1), Sheet1!$X:$X, 0)))
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    07-12-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Data from master worksheet auto filtered onto different worksheet

    Wow, I'm impressed on several levels. You both answered so quickly and precisely and proved me wrong by solving the issue with no code.

    Both solutions work good but I'm going to have to go with fozguy's. I really like the fact that you can just add or delete rows without worrying about the second sheet being updated.

    I did notice though that inserting a row right over the "black" row 19 doesn't do the trick (and the added row doesn't keep the format but that's no biggy). When that is done, the formulas in Sheet2 don't update. Since the last row that is checked is row 18, I would set row 18 as the dummy row and make sure that everything inserted is done above row 18.

    Besides this detail, it seems to work great and I think it will do the trick! Thank you so much for both of you.

  5. #5
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Data from master worksheet auto filtered onto different worksheet

    Hi Guillaume;

    If you're interested, I like JB's solution better. It's easier to see how it works. I spent 20 minutes studying mine to make sure that it worked, but it took me less than 30 seconds to see that JB's worked. I think there's just one bug in it
    If Sheet2!B2 : =MAX(Sheet1!X:X)
    then the formula should be
    =IF(ROW(A1)>$B$2, "", INDEX(Sheet1!A:A, MATCH(ROW(A1), Sheet1!$X:$X, 0)))

    BTW; neither solution really stands up if you insert or delete rows in the raw data. You would need to use Offset() for every formula in column X on sheet 1.
    JB's formula in X9 : =Offset(X9,-1,0)+IF(B9="Approved",1,0) (minor improvement).
    Mine would have to be modified the same way. Every place in the formula of X9 that referred to X8 would have to be replaced with Offset(X9,-1,0).
    So my formula in X9
    X9 : =X8+MATCH("Approved",INDIRECT(CELL("Address",OFFSET($B$9,X8,0))&":$B$65535"),0)
    would be changed to
    X9 : =Offset(X9,-1,0)+MATCH("Approved",INDIRECT(CELL("Address",OFFSET($B$9,Offset(X9,-1,0),0))&":$B$65535"),0)
    Then you can add & delete rows with no concern for the formulas crashing.

    My formulas on Sheet2 would have to be changed also to accommodate added or deleted rows, and they would be a lot harder to modify. But JB's don't need to be modified at all.
    Last edited by foxguy; 07-13-2010 at 01:06 PM.

  6. #6
    Registered User
    Join Date
    07-12-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Data from master worksheet auto filtered onto different worksheet

    After playing a little more I agree with you. Those are both very clever solutions. I do like the simplicity in JB's solution, I'll give it a shot.

    Thanks again

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Data from master worksheet auto filtered onto different worksheet

    Go team!...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1