Pulling text through from other sheets

I have a project risk and issues Log and want to pull through the top 5 risk and issues through to another sheet (based on risk score).

The risk and issue Log have a text description (there is a risk that....) and a risk score (5).

I understand cell linking but this won't work as the top 5 risk and issues will change on a regular basis.

Is there a way to perform a lookup (vlookup?) and to pull the top 5 risk and issues through to another sheet automatically?

I need to capture the 'text description' of the risk or issue and the score without having to copy the data from one sheet to another.

Happy to share sample sheets to further explain what I'm trying to achieve.

Thanks in advance.

Phil

Find more posts tagged with

Formulas

Reports

Dashboards

Project Management

Comments

Jim Hook

Phil,

Smartsheet recently introduced two new advanced functions, SMALL and LARGE, that allow you to scan through a range of cells and pick out the 1st largest, 2nd largest, or smallest, etc. You would probably have to use LARGE inside a LOOKUP or VLOOKUP function so that you could return the text and a similar formula in the next (or previous) column to pick up the risk level. You have to specify which smallest or largest value to return as part of the SMALL or LARGE function and I usually use an index column when I'm bringing in data with sequential numbers, from 1 to 5 in your case. That keeps all the equations the same in each row. Just remember to make the function ranges absolute by using the $ sign.

Phil Smith

Thanks for this, sounds very much like what I am trying to achieve.

I think I understand the lookup against the numbers but I can't see how I can pull the text through in the adjacent column.

C1 C2

This is text 4

This is text 2 5

This is text 3 1

This is text 4 3

I want to be able to find the highest 2 scores in C2 (as an example) and pull them through to my other sheet together with the associated text that is in C1. Ideally, I want to keep the two columns separate on the other sheet.

Any examples (even just for the above) would be really useful to give me something to go at.

Thanks again

Jim Hook

Phil, the LOOKUP and VLOOKUP functions allow you to specify which column of the referenced array (the range of rows and columns you're dealing with) to bring back to the current cell. It's a little tricky at first since both functions require the search value to be in the first column of the array and once that is found it can bring back the value in the second column, for example, that would be the text. Therefore, the table above in your example won't work because the item you're searching for is in the second column. If you reverse those two columns so the number is on the left of the text (or copy the text into the column to the right of the risk number) then the formula below would return the text after you have rearranged the risk number and text to put the risk number in column 1 of the reference range.

row 1 C2 C1

row 2 4 This is text

row 3 5 This is text 2

Row4 1 This is text 3

Row5 3 This is text 4

=LOOKUP(5,$[C$2]2:$[C1]$5,2,false)

The formula above would return "This is text 2" since it looks for a 5 in the first column and returns the value in column 2 of the reference. This formula could be put anywhere in the destination sheet and it would still return the same thing. I don't have time right now to go into more detail about how to use the LARGE function to find the largest risk number. There may be an issue if there are two, or more, level 5 risks in the data since it would probably pick up the first one both times. The only way I know around that is to create another column where you add a small value (something like 0.001 times the row number) so that the first 5 would be slightly smaller than the second 5, if you get what I mean. That way the second 5 would be the largest number and the first 5 would be the second largest number. Sorry if this is confusing. I should have time tomorrow if you still have issues getting it to work.

Phil Smith

I now have each part working individually. The top xx entries are working well with the additional multiplier (0.00x) and I can also pull through the associated text for each entry.

What's the best way to put the two formula's together? Should I be using the LARGE function first and then VLOOKUP or the other way around?

Again, any examples would be really useful.

Thanks

Phil

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…