Linking row data together into new sheet.

GracePendlebury

Hi All! 

So I have a sheet with a list of tasks, their due dates and a checkbox column for when they are completed. I'm trying to set up some formulas in another sheet (my metrics sheet which feeds to a dashboard) to generate a top 5 overdue tasks list by searching the list for the most overdue dates. 

I've managed to get the dates working with a SMALL(COLLECT...) function which checks for a ticked checkbox so as to only consider dates of unchecked/incomplete tasks, but I'm struggling to get the cell next to the dates to display the task name which is in the same row as the date in the original reference sheet. This is the formula I currently have:

=VLOOKUP([Column with my overdue dates in metric sheet]3, {Reference Task List look up table which includes both the due date and task name columns}, 2, false)

This is working, but I've found a problem when I have two overdue tasks with the same due date. The formula will only search until it finds the first row with the date, it's not specific to the row the actual date came from, thereby repeating the task name for a different one. I've included a screenshot of what this looks like. 

I know that this can be achieved using a report, however for the number of projects my team have operating, creating new reports for every one will get very cluttered, so I'm looking for a neater sheet to sheet link.

I'm still quite new to smartsheet and would appreciate any suggestions or help in solving this. I've tried playing around with the INDEX and MATCH functions but haven't had any luck with getting them to work.

Thanks!

Grace

Paul Newcome

Because of duplicated dates, you will still run into the same issue with INDEX/MATCH. INDEX/MATCH is definitely much better than a VLOOKUP though.

 

I have a few ideas on how this might work, but I will have to get back to you after some testing unless someone else is able to provide a solution before then.

Paul Newcome

Here is my suggestion...

 

On your Source Sheet add a column that I will just call "ID" for this example. In the first row of this column, you would enter

 

=VALUE(YEARDAY(Date@row) + "." + COUNTIFS(Date$1:Date@row, Date@row))

 

then dragfill down the rest of the rows.

.

The simplified explanation is that it will take the YEARDAY of the date and then add a decimal and another number based on how many times that particular YEARDAY appears then converts it to a numerical value that can then be used in other formulas.

 

The lower the number, the older the date.

.

Then on your metrics sheet, add another column that I will also call "ID" (since it's on a different sheet, I like to try to name columns the same to represent the data they are pulling for easier reference).

 

I am also going to assume that the column in your metrics sheet where you have 

1

2

3

4

5

is called "Rank". You will obviously need to adjust this to reflect the correct column name.

.

In the Metrics Sheet ID column, you would enter this:

 

=SMALL({Source Sheet ID Column}, Rank@row)

 

This will populate the number from the ID column in your Source Sheet based on first smallest, second smallest, etc according to the number in the Rank column.

.

Now you can work on pulling the actual data you want. I will show an example of the INDEX/MATCH.

 

To pull the date for rank 1 based on the ID column, you would use something along the lines of this...

 

=INDEX({Source Sheet Date Column}, MATCH(ID@row, {Source Sheet ID Column}, 0))

.

Task Name would look like this...

 

=INDEX({Source Sheet Task Name Column}, MATCH(ID@row, {Source Sheet ID Column}, 0))

.

And then just continue with this pattern until you have pulled the data from each of the desired columns. You can then dragfill all of your INDEX/MATCH formulas down and they will automatically update for the appropriate ranking.

GracePendlebury

Hi Paul,

Thank you so much for your help!

I have run into trouble at the first step though... 

This is the exact formula I gave my ID column:

=VALUE(YEARDAY([Due Date]7) + "." + COUNTIFS([Due Date]7, [Due Date]7))

I dragged it down to the rest of the section, but as you can see in my screenshot, the ID number is still the same when the due date is the same, which I don't think should have happened based on your description? Admittedly, I don't really understand the finer logic of these formulas, just their function, so if I've done something wrong here I can't see what it is :')

Paul Newcome

Ensure that you have the row references EXACTLY how I entered them with the @row references and using the $ to lock the one row reference in. That is the key to making this formula work as intended.

GracePendlebury

Hi Paul,

I adjusted my formula to match exactly and it worked perfectly! Thank you.

One more question though: I have dates ranging through 2019 and then into early 2020. The 2020 dates are being given the smallest numbers and thus popping up as my most overdue tasks even though they're actually the furthest away. It's like only the day and month are evaluated and the formula doesn't pick up that the year is larger.

Any ideas on how to fix this?

Paul Newcome

Yes. We just need to change up the way we are using the date to generate a number. (And you are correct that YEARDAY does not account for the actual year). So instead of using YEARDAY, we are going to use a combination of YEAR, MONTH, and DAY in that order to generate a numerical value following a yyyymmdd format then continue with our COUNTIFS.

.

So the bold portion is what we will focus on.

 

=VALUE(YEARDAY([Due Date]@row) + "." + COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row))

.

The YEAR portion is easy. It will ALWAYS be four digits, so we can start with

 

YEAR([Due Date]@row) +

.

Now we want to make sure all of these numbers are being pulled as text strings so the are joined together instead of added together.

 

YEAR([Due Date]@row) + "" +

.

For the month, we always want two digits, so we need to account for this by adding a leading zero if the month number is less than ten. To do this we use an IF statement to say that if the month number is less than 10, generate a 0, otherwise leave blank, then generate the month number.

 

YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, 0) + MONTH([Due Date]@row) +

.

The day number will be the same.

 

YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, 0) + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, 0) + DAY([Due Date]@row

.

So now we will generate a number in yyyymmdd format that will ensure that the most future dates have an ID higher than the most past dates. Dump this in to the current ID formula that accounts for duplicate days.

 

=VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, 0) + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, 0) + DAY([Due Date]@row+ COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row))

.

Just remember to use the @row references and the $ where appropriate, and you should be pretty well set. Give it a whirl and let me know.

GracePendlebury

Hi Paul,

I'm back again...

That has definitely worked a lot better! But if you take a look at the screenshot I've attached, tasks due in June are being listed above April. I'm coping in word for word the formulas I have involved in this, and hopefully you can see if I've made an error:

 

To generate a Task ID in my source sheet:

=VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, 0) + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, 0) + DAY([Due Date]@row + COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row)))

 

To pull my generated Task IDs into my metrics sheet according to most overdue:

=SMALL({Source Sheet Task ID Column}, [Column7]@row)

 

To pull through the due date matching the task ID into my metrics sheet:

=INDEX({Source Sheet Due Date Column}, MATCH([Task ID]@row, {Source Sheet Task ID Column}, 0))

 

To grab the Task Description the matches that ID as well:

=INDEX({Source Sheet Task Description Column}, MATCH([Task ID]@row, {Source Sheet Task ID Column}, 0))

 

I really appreciate your help with this, it's been invaluable!

 

Thanks,

Grace

Paul Newcome

Double check your Task ID. It looks like it is only generating off of year and month and not including the day before count.

 

It also looks like your count can go above 9 (meaning more than one digit) which is something I did not account for.

 

What is the maximum number of tasks for any given day? I just need to know how many digits to account for.

 

Will it be less than 100 meaning we only need 2 digits? Less than 1,000 meaning a possibility of 3 digits?

GracePendlebury

Hey Paul,

I believe the most tasks due on one day is 10, so should I be using <100? If you take a look at this latest screenshot of my source sheet, the Task ID does seem to be accounting for the day with 2 digits, but those cells down the bottom in this picture and others in my sheet break from the pattern. In each case these dates are approaching the end of the month (29th & 27th etc), could this have something to do with it?

I also have another issue. In the two smaller screenshots I've captured how my sheet works. For every task, I am tracking it's current due date status, and then when it's checked as complete, On Time Delivery columns record whether it was completed on time or overdue as well as how many days either before or after the due date that happened.

The problem is that if a particular task from this standard list isn't applicable to the project, we write N/A in the yellow "Weeks Prior to Due Date" column. This stops the other metrics to the right from continuing to tick over and skew the dashboards with "overdue" tasks that actually don't need to be done. I've noticed that if I write N/A in there now, every Task ID cell from that row downwards becomes invalid. Is there an easy fix to the Task ID formula which will prevent this? I do really need to be able to discount irrelevant tasks as this tool is a standard template within our company. 

Paul Newcome

I see what is happening with the ID Calculation. It looks like there is a misplaced parenthesis causing the DAY calculation to be added to the COUNTIFS. That's an error on my part.

Note the closing parenthesis being moved from the end of the formula to before the COUNTIFS.

 

=VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, "0") + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, "0") + DAY([Due Date]@row) + COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row))

.

To ensure that all ID's are of the same length, we will use an IF statement on the COUNTIFS the same way we did for the DAY and MONTH statements.

 

=VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, "0") + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, "0") + DAY([Due Date]@row) + IF(COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row) < 10, 0) + COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row))

.

So the above should be the formula with my mistake corrected and the potential for more than 10 (but not more than 99) tasks in a day.

.

To account for N/A's... The problem is that the YEAR, MONTH, and DAY calculations are looking for a date. We know that we are generating ID's to find the low number, and we also know that our ID's are 10 digits. So we will say that if the cell is "N/A", generate a number that is 11 digits long, otherwise run the ID formula.

 

=IF([Weeks Prior to Due Date]@row = "N/A", "99999999999", otherwise run the ID formula)

 

Which turns into this:

 

=IF([Weeks Prior to Due Date]@row = "N/A", "99999999999", VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, "0") + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, "0") + DAY([Due Date]@row) + IF(COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row) < 10, 0) + COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row)))

GracePendlebury

Hi Paul,

The revised ID calculation is working perfectly!

The fix for adding "N/A" doesn't seem to function however. If you take a look at my screenshot, it is generating the 11 digit number, which is great, but all the cells below are still becoming invalid. I think it has something to do with the way the whole due date column connects, because if I delete the sections in bold below, the IDs come back for the all the cells below.

=IF([Weeks Prior to Due Date]@row = "N/A", "99999999999", VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, "0") + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, "0") + DAY([Due Date]@row) + IF(COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row) < 10, 0) + COUNTIFS([Due Date]$1:[Due Date]@row, [Due Date]@row)))

This obviously makes the formula irrelevant though because it will generate the same ID for replicate dates again.

Any ideas?  

Paul Newcome

Ok. I think I've got it figured out now (hopefully). When we are looking at the ranges in our COUNTIFS, we are still including the "N/A"'s. We will include logic in our COUNTIFS to ignore them and only consider dates.

 

=IF([Weeks Prior to Due Date]@row = "N/A", "99999999999", VALUE(YEAR([Due Date]@row) + "" + IF(MONTH([Due Date]@row) < 10, "0") + MONTH([Due Date]@row) + IF(DAY([Due Date]@row) < 10, "0") + DAY([Due Date]@row) + IF(COUNTIFS([Due Date]$1:[Due Date]@row, IFERROR(DATEONLY(@cell), 0) = [Due Date]@row) < 10, 0) + COUNTIFS([Due Date]$1:[Due Date]@row, IFERROR(DATEONLY(@cell), 0) = [Due Date]@row)))

.

Give this a whirl and see how it does for you.

GracePendlebury

Hey Paul,

Unfortunately that's thrown back #INVALID OPERATION

Do you have a contact (maybe email) that I could send a version of my sheet to? This might be easier to work out if you could see the formulas in action.

Paul Newcome

Ugh. I don't know why I missed it. The problem is that your actual Due Date in the rows where you have an N/A is coming back as invalid. If you correct that error, you should be fine.