+ Reply to Thread
Results 1 to 21 of 21

Create a four-column sorting table that updates based on calculations.

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Arrow Create a four-column sorting table that updates based on calculations.

    Hello board,

    I have a project in which I'm tracking clients who, at any given time, will fall into one of four phases of the project: Pending, Eligible, Enrolled, and Completed.

    I have a formula table set-up that uses input dates to calculate which phase (Pending, Eligible, Enrolled, Completed) a participant is in at a given time, and then assigns one of the four phases in Column F based on these calculations.

    Attachment 455734

    What I want is to generate a table with four columns - one for each phase - beneath which Excel will send the ID's. Ideally, the columns of this new table would update if a client changes phases. Something like this:

    Attachment 455739

    I do not believe that VLOOKUP, for example, can perform this task. Any help would be greatly appreciated.

    Sorry for redactions in shared images.
    Last edited by arwebb0264; 04-12-2016 at 12:46 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Create a four-column sorting table that updates based on calculations.

    Sounds like you may need a Pivot Table but upload the actual workbook rather than a picture and no doubt we can help further.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Quote Originally Posted by Richard Buttrey View Post
    Sounds like you may need a Pivot Table but upload the actual workbook rather than a picture and no doubt we can help further.
    Okay- how can I upload my WB here, Richard? I avoided doing so initially because I am sensitive about private info.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    Remove any sensitive data from your file and to upload:

    Click "Go Advanced" then "Data Management"

  5. #5
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Quote Originally Posted by JohnTopley View Post
    Remove any sensitive data from your file and to upload:

    Click "Go Advanced" then "Data Management"
    Will this do? I was unable to locate 'Data Management' in the posting panel.

    16-xxx U54 Tracker Schema_safe.xlsx

    The first sheet in that book ('Tracker') is the first photo from my OP and contains the calculations that determine which status is assigned in Column F.

    The second sheet ('StatusTable') is where I would like to have Excel sort all the Codes by their status - as defined in Tracker!ColF. I have never used a pivot table, but I am willing to learn if that is the best way to go!

    Thank you Richard. Rep + for repeatedly being patient with myself and other new users.
    Last edited by arwebb0264; 04-12-2016 at 01:23 PM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    In C3 of "STATUSCHART"

    =IFERROR(INDEX(Tracker!$A$3:$A$100,SMALL(IF(Tracker!$F$3:$F$100=StatusChart!C$1,ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

    Enter with Ctrl+Shift+Enter

    For E, G and J change C$1 to E$1 etc

  7. #7
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Quote Originally Posted by JohnTopley View Post
    In C3 of "STATUSCHART"

    =IFERROR(INDEX(Tracker!$A$3:$A$100,SMALL(IF(Tracker!$F$3:$F$100=StatusChart!C$1,ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

    Enter with Ctrl+Shift+Enter

    For E, G and J change C$1 to E$1 etc

    John - thank you for the response. When I entered that formula into C3 with Ctrl+Shift+ENTER, I got a windows explorer window titled "Update Values: StatusChart" where I am presumably being prompted to upload an .xls.

    Is there another step to this beyond the quote above? Thanks again!

  8. #8
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    John - disregard the previous issue (had a typo, fixed it, formula now entered).

    My question is where you specify C$1... Should it be C$3 instead, as C1 is a frozen header and C$3 is the beginning of the field?

  9. #9
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Richard,

    I was reading about pivot tabled given your suggestion before, but now that I have uploaded the workbook: do you think that JohnTopley's formula will be able to accomplish this same function? I have entered his - still working through it to see if I can make it work, but so far I do not see how this will be able to identify the Status from 'Tracker!ColumnF' and sort them by status in the columns I made in 'StatusChart!'

    Thoughts? I feel I am stuck here

    In C3 of "STATUSCHART"

    =IFERROR(INDEX(Tracker!$A$3:$A$100,SMALL(IF(Tracker!$F$3:$F$100=StatusChart!C$1,ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

    Enter with Ctrl+Shift+Enter

    For E, G and J change C$1 to E$1 etc

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    I Haven't 2013 version but I don't understand why you get such a message.

    You should just copy formula into the cell then Hold down CTRL+SHIFT keys and hit ENTER.

    See attached,
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    .... re C$1: it uses the header (row 1).as a comparator against the codes in Status in TRACKER. Note in your test file "Eligible" had an extra blank in the header and the comparison would fail. Corrected in my workbook.

  12. #12
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Quote Originally Posted by JohnTopley View Post
    .... re C$1: it uses the header (row 1).as a comparator against the codes in Status in TRACKER.
    Clever, I can see how that is operating now!

    Quote Originally Posted by JohnTopley View Post
    Note in your test file "Eligible" had an extra blank in the header and the comparison would fail. Corrected in my workbook.
    Because I had to redact a lot of info from the WB I uploaded, it is easier for me to insert changes back into my OG book; therefore, I am searching for this extra blank in an "Eligible" header... otherwise John, I am very pleased to say that your solution is working! Very many props, and thank you much.

  13. #13
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    ...annnnnnd found it. I still find it impressive how some of the veteran members of this forum are so freaking quick at identifying such small details in other people's work.

    Mods: Issue Solved.
    JohnTopley Rep ++

  14. #14
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    response-16-xxx-u54-tracker-schema_safe.xlsx

    Ah, so it turns out there will be one more step to make this table function properly.

    (I have uploaded JohnTopley's version of my WB here)

    You will see that each unique Code in 'Tracker!' has associated date information in its row. For this last step, I will describe what is needed with Code '5002TL' as an example.

    Code 5002TL is listed as "ENROLLED," and appears correctly beneath the ENROLLED column in 'StatusChart!'; however, I need 5002TL's associated info from 'Tracker!' - in this case, the "1st Assessment Date" from 'Tracker!' (cell T4) needs to follow 5002TL to 'Pipeline!' (cell G3).

    The obvious issue is that, because the Codes may go from "Pending" to "Eligible" over time, I cannot simply put =Tracker!T$4 in Pipeline!G$3; if I do, the date will be stuck in G$3, even if the Code associated with that date has moved to another column.

    So what I need is for associated data to fluidly move along with the Codes (between Pending, Eligible, Enrolled, Completed). To make it simple, you can just work with the one example I gave above & I will try translating it for other associated values.

    We would know that we've succeeded if 5002TL & it's associated 1st Assessment Date relocate themselves to a new column, together, when their STATUS is changed from "Enrolled" to something different.
    Last edited by arwebb0264; 04-13-2016 at 12:17 PM.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    Put this in H3 in "StatusChart"

    =IFERROR(VLOOKUP($G3,Tracker!$A$3:$Z$100,20,0),"")

    in I3

    =IFERROR(VLOOKUP($G3,Tracker!$A$3:$Z$100,22,0),"")

    Copy both down to row 100

    I changed status of 5002TL : see attached.

    =IFERROR(VLOOKUP($G3,Tracker!$A$3:$Z$100,22,0),"")

    You might want consider a dropdown Data Validation list for your Enrolment Status column
    Attached Files Attached Files
    Last edited by JohnTopley; 04-13-2016 at 01:03 PM.

  16. #16
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Wow, pretty slick. I will look into a dropdown Validation list once I have the nuts and bolts correct.

    I was able to successfully apply your formula.

    You've been super helpful, and so I promise this will be my last little bit:

    Current workbook - 16-xxx-u54-tracker-schema_safe_3.xlsx

    If you look at Pipeline!B4 as an example, You will see that there is a place to stick the 'Date READY if PENDING' for the Code next to it in Pipeline!Col(A).

    My issue is that there are two different opportunities for a Code to be "Pending" - Column O and Column R from 'Tracker!'. So I need the function in Pipeline!Col(B) to:

    1) Check if Tracker!Col(O) is "READY,"
    2) if "READY" then show the Date in Tracker!Col(R)
    3) if ~"READY" then show the Date in Tracker!Col(0)

    Believe it or not, I always try my best before asking! Here is what I tried in Pipeline!B4:

    =IF((VLOOKUP($A4,Tracker!$A$3:$Z$103,15,0)="READY"),IFERROR(VLOOKUP($A4,Tracker!$A$3:$Z$103,18,0),IFERROR(VLOOKUP($A4,Tracker!$A$3:$Z$103,15,0),"")))
    You can see in the WB, the output reads "FALSE" in Pipeline!B4.

    Thank again John

  17. #17
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    =IF((VLOOKUP($A4,Tracker!$A$3:$Z$103,15,0)="READY"),(VLOOKUP($A4,Tracker!$A$3:$Z$103,18,0)),(VLOOKUP($A4,Tracker!$A$3:$Z$103,15,0)))
    This actually worked for me so long as there is a value available in Pipeline!Col(A). So now I am trying to nest an AND function to activate the formula only if Pipeline!Col(A) > 0... I think?

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    Try

    =IF(VLOOKUP($A4,Tracker!$A$3:$Z$103,15,0)="READY",VLOOKUP($A4,Tracker!$A$3:$Z$103,18,0),VLOOKUP($A4,Tracker!$A$3:$Z$103,15,0))

    IFERROR is redundant because there must a valid VLOOKUP results as the CODE must be in TRACKER to appear in PIPELINE.

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    this ...

    =IF(A3="","",IF(VLOOKUP($A3,Tracker!$A$3:$Z$103,15,0)="READY",VLOOKUP($A3,Tracker!$A$3:$Z$103,18,0),VLOOKUP($A3,Tracker!$A$3:$Z$103,15,0)))

  20. #20
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: Create a four-column sorting table that updates based on calculations.

    Solved.

    /Thread (and my boss thinks I am much smarter than I am).

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Create a four-column sorting table that updates based on calculations.

    or ...to be consistent with other VLOOKUP

    =IFERROR(IF(VLOOKUP($A3,Tracker!$A$3:$Z$103,15,0)="READY",VLOOKUP($A3,Tracker!$A$3:$Z$103,18,0),VLOOKUP($A3,Tracker!$A$3:$Z$103,15,0)),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to create a list that dynamically updates based on other selections
    By DAN.DEJONG84 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2015, 04:58 PM
  2. [SOLVED] How to Create a Rolling Summary Page that Automatically Updates from a Data Table
    By DontExcelAtMuch in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-28-2013, 03:34 PM
  3. [SOLVED] Create Dynamic Column Lookup Reference based on Table Header
    By jeversf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 12:49 PM
  4. Need Help Sorting Table based on a Column Value in Excel 2007
    By JesWithOneS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-19-2012, 03:44 PM
  5. Create chronological table from daily updates
    By shockeymoe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2011, 11:45 AM
  6. Replies: 3
    Last Post: 03-04-2011, 06:01 AM
  7. sorting a column with calculations
    By aaroncigar in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2008, 06:00 PM

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