Pulling text through from other sheets

Phil Smith

Hi

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

 

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

Hi

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

Hi

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