Referencing the first 15 names in a list (a list that is manually altered bycopy+paste,etc

Hi Ron,

Thank you so much for your reply! So how would I reference other cells? does the number '1048576' have a specific meaning? I suppose 3 stands for third row, while 2 for second column, right? If I understand how to use this function, I think I'll get what I was looking for.

In the meantime I'll start experimenting

Thank you for all your great advice! I really appreciate it!

Have a nice day!

Regards,

Peter

Hi guys,

One more thing. Would you happen to know of a version where I can just "pull down" the function for the next cells (I'll need to work with a large number of rows, so it would be rather time consuming to write in the function to every cell).

Thank ya!

Peter

Thanks for getting back to my issue!

Attached is the file in question.

I'll try to explain the situation:

what will happen is that the sheet titled "input" will be manually populated. names will be written in column B. then from a dropdown list categories, subcategories, likelihood and impact will be selected in the columns I, J, K, M respectively. All this during a meeting with people. Then all these names will be ranked and sorted according to rank. there may also be some copy pasting involved. This was problematic, because my task was to make excel display the different names in a table according to likelihood and impact. In the original version things got messed up because of the copy paste and sorting. So this is why I first created a new sheet (Background1 - references) where the first 15 names (and all the other data from the row) were listed. Later on I referenced this new list for my table. This way things worked out with copy pasting too. I used this formula: "=INDEX('MY SHEET'!$1:$1048576,3,2)" (although this seemed to work fine too: "=INDIRECT("'Sheet1'!I4")). The thing with this option was that I had to manually change the numbers for every new cell. I couldn't do the usual "pull down" thingy with excel. Now I was asked to do the same thing, but with a significantly larger number of names, so this way manually changing them would be kind of problematic. So I was just wondering if there was a way to automate this process in some way.

Thanks in advance. I hope I managed to make myself clear.

In case you have some additional capacity and are interested, I would ask another question.

So once my list is done, I will have names with certain likelihoods and impacts. all together there are 25 combinations for these (5 types of likelihood, 5 types of impact). for every combination there is a value assigned. My question is, is there a way to create 25 columns where I could list all the names for a given value? for example it looks at my list of maybe 2000 names, and lists all of those which have number 500 as an assigned value (for example for combination of great likelihodd - great impact)

Thank you once again for all the help, I really appreciate it. If you need any more information, let me know!

Best,

Peter

no problem, don't worry bout it. I know it's kind of confusing, It took me some time just to figure out how to write it down. The function didn't really work out for me, but don't worry bout it! Get well soon!

Sorry, my bad, now it seems to be working!!!! Thank you very much!!!