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

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

Hi everyone!

At my work, I encountered the following task. There is a list of items, with several attributes to them in the same row. during oral presentation, the order of these rows is manually changed, according to feedback from participants (copy+paste), or sorted / filtered by excel. What I would need is to have a separate spreadsheet, that lists the top 15 rows of this list. I referenced the given cells in the following way: "IF('Sheet1'!I4="",""",'Sheet1'!I4). The 'IF' is there to write nothing if the cell is empty. This solution works out fine for me if there is no copy pasting and such. Once copy pasting starts, all the references become messed up.

So my question would be, how can I reference given cells of a sheet, so that it gives the value in that particular cell, no matter what. e.g. if i cut something out of it and paste something else from a different row, then it should show the new value and with copy pasting, the references should not 'move'.

I hope this makes sense to someone. If anyone could help me out with this one, I'd greatly appreciate it!

Peter

2. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

This formula will ALWAYS reference cell B3 on a worksheet named "MY SHEET"
=INDEX('MY SHEET'!\$1:\$1048576,3,2)

Is that something you can work with?

3. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

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

4. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

1048576 is number of the last row.

5. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

or this

=INDIRECT("'Sheet1'!I4")

That will always look at that cell. (but it is volatile)

6. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

Have a nice day!

Regards,

Peter

7. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

glad to help remember to mark it solved

8. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

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

9. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

depends how your infomation is stored?

if you upload a book with an example of what you need i can take a look and see

probably using index and the row() function in some way

10. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

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

11. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

to be honest im feeling pretty unwell to day and am really not able to think clearly.

i think this will do what you need in the keeping cells part but i cant even figure out what your after im really sorry but maybe someone else can

``Please Login or Register  to view this content.``

12. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

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!

13. ## Re: Referencing the first 15 names in a list (a list that is manually altered bycopy+paste

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

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

#### 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