Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-3504552%22%20slang%3D%22en-US%22%3EFormulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3504552%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20someone%20offer%20some%20advice.%20Basically%20I%20am%20trying%20to%20get%20the%20name%20in%20Column%20D%20to%20appear%20in%20another%20tab%20'Filed'%2C%20if%20the%20text%20in%20Column%20M%20states%20'filed'.%20I%20should%20also%20say%20that%20there%20several%20different%20types%20under%20WF%20Status%2C%20not%20just%20Filed%20and%20WIP.%20Would%20anyone%20know%20a%20formula%20that%20would%20work%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F380190iAF233FCA77041E3E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel.png%22%20alt%3D%22Excel.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3504552%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3504631%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3504631%22%20slang%3D%22en-US%22%3EI%20suggest%20you%20look%20into%20pivot%20tables%20but%20if%20you%20have%20Excel%20365%20then%20you%20can%20also%20use%20FILTER()%3A%3CBR%20%2F%3E%3DFILTER(D2%3AD4%2CM2%3AM4%3D%22Filed%22%2C%20%22%22)%3CBR%20%2F%3Eor%20preferably%20use%20table%20references%3A%3CBR%20%2F%3E%3DFILTER(Table1%5BClient%20name%5D%2C%20Table1%5BWF%20Status%5D%3D%22Filed%2C%20%22%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3504877%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3504877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bso%20I%20have%20tried%20this%2C%20but%20it%20is%20spilling%20the%20same%20name%20then%20into%20every%20row%3F%20(see%20image%20attached.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20what%20I%20intend%20to%20use%20this%20for%2C%20is%20that%20I%20currently%20have%20an%20online%20excel%20spreadsheet%20which%20I%20use%20as%20a%20workflow%20tracker%20for%20all%20our%20clients.%20I%20then%20have%20various%20meetings%20per%20week%2C%20where%20I%20check%20the%20status%20of%20all%20work%20and%20update%20the%20spreadsheet%20accordingly.%20However%2C%20one%20of%20my%20colleagues%20has%20now%20requested%20that%20I%20give%20her%20a%20list%20of%20all%20work%20that%20has%20been%20completed%20i.e.%20'filed'%20after%20every%20meeting%2C%20so%20fees%20could%20be%20raised.%20So%2C%20I%20was%20hoping%20to%20simplify%20this%2C%20so%20that%20every%20time%20I%20marked%20a%20client%20'FILED'%20on%20my%20spreadsheet%2C%20their%20name%20would%20automatically%20appear%20on%20a%20separate%20tab%2Fsheet%2C%20that%20I%20would%20then%20give%20my%20colleague%20access%20to%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%202.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel%202.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel%202.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F380206iCB9EAF4EF2E038B9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel%202.JPG%22%20alt%3D%22Excel%202.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3507978%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3507978%22%20slang%3D%22en-US%22%3Eit%20looks%20like%20it%20did%20what%20you%20asked%3A%20give%20list%20of%20client%20names%20marked%20'FILED'.%20Do%20you%20have%20additional%20criteria%20like%20a%20date%20range%20or%20only%20want%20UNIQUE()%20names%20returned%3F%20I'm%20not%20sure%20what%20you%20have%20and%20need%20so%20it%20is%20hard%20to%20tell%20you%20how.%20Can%20you%20attach%20the%20workbook%20or%20give%20a%20link%20to%20it%3F%20(please%20NO%20confidential%2Fprivate%2Fpersonal%2Fetc...%20info)%3C%2FLINGO-BODY%3E
New Contributor

Good afternoon,

 

Could someone offer some advice. Basically I am trying to get the name in Column D to appear in another tab 'Filed', if the text in Column M states 'filed'. I should also say that there several different types under WF Status, not just Filed and WIP. Would anyone know a formula that would work?Excel.png

3 Replies
I suggest you look into pivot tables but if you have Excel 365 then you can also use FILTER():
=FILTER(D2:D4,M2:M4="Filed", "")
or preferably use table references:
=FILTER(Table1[Client name], Table1[WF Status]="Filed, "")

@mtarler so I have tried this, but it is spilling the same name then into every row? (see image attached.)

 

Basically what I intend to use this for, is that I currently have an online excel spreadsheet which I use as a workflow tracker for all our clients. I then have various meetings per week, where I check the status of all work and update the spreadsheet accordingly. However, one of my colleagues has now requested that I give her a list of all work that has been completed i.e. 'filed' after every meeting, so fees could be raised. So, I was hoping to simplify this, so that every time I marked a client 'FILED' on my spreadsheet, their name would automatically appear on a separate tab/sheet, that I would then give my colleague access toExcel 2.JPG

it looks like it did what you asked: give list of client names marked 'FILED'. Do you have additional criteria like a date range or only want UNIQUE() names returned? I'm not sure what you have and need so it is hard to tell you how. Can you attach the workbook or give a link to it? (please NO confidential/private/personal/etc... info)