+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    13

    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!

    Thanks in advance!

    Peter
    Last edited by setilvenstre; 10-16-2012 at 07:42 AM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    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?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    13

    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. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,547

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

    1048576 is number of the last row.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    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)
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  6. #6
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    13

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


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

    Have a nice day!

    Regards,

    Peter

  7. #7
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    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. #8
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    13

    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. #9
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    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. #10
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    13

    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
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    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. #12
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    13

    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. #13
    Registered User
    Join Date
    10-02-2012
    Location
    budapest
    MS-Off Ver
    Excel 2010
    Posts
    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!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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