+ Reply to Thread
Results 1 to 23 of 23

copy cell value based on another cell value in range

  1. #1
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    copy cell value based on another cell value in range

    if you open the book you will see what I envision to have when I am done although key formulae are missing. I tried to use array formula to evaluate for cell values in column A but they would not work. I would like the top page to be the data entry page and the pages below to be where the data is evaluated for each nurse. I need to know how to search column A in the top page for a certain value (name). If that value is found all data in that row would populate to the corresponding cell on the nurse's page. Visually the book is set up how I want it to work however I need help with the formulae that would import the data from the top sheet.naughty list form example specific nurse.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: copy cell value based on another cell value in range

    Try this ARRAY formula in sheet 2B2, copied down and across...
    =IFERROR(INDEX('Sheet1 (2)'!$A$2:$T$40,SMALL(IF('Sheet1 (2)'!$A$2:$A$40="John Doe",ROW('Sheet1 (2)'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Sheet1 (2)'!$A$1:$T$1,0)),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    If you have the name somewhere (with an exact match), then you could reference it, instead of hard-coding it into the formula
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by FDibbins View Post
    ........
    If you have the name somewhere (with an exact match), then you could reference it, instead of hard-coding it into the formula
    .. Do you know if there is a way to get at / access the tab name from a formula?????
    Last edited by Doc.AElstein; 08-16-2015 at 07:51 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    . I was lurking through this post and found that formula given in Post #2 very useful, especially the way it selects based on the headings selections. It is probably the ideal solution for you
    . But just to give something back to the Thread: An alternative VBA code solution: The following code will do the same job without formulas.. This code starts automatically when you select the sheet of the Naughty Nurse. It then updates the sheet based on the details in the first sheet giving identical results to FDibbins Formula.
    . Note the code must be in each Naughty Nurse sheet module, not in a normal Macro module – to do that
    . 1 ) Copy the code complete to the clipboard.
    . 2 ) Right mouse click on a Naughty Nurse sheet tab
    . 3 ) select an option something like “Show Code” ( I do not know exact English wording as my Excelis German )
    . 4 ) You should now see the VB Development Environment Window shown.
    . 5 ) Paste in the code in the large Empty code Window.
    . 6 ) Hit Alt + F11 to get out of the VB Development Window and back into “normal” Excel Spreadsheet Window
    . 7) Repeat steps .2) to .6) For each Naughty Nurse
    . 8 ) Resave your file, but select the ( usually second ) option of something like “Excel Workbook with Macros”
    . 9 ) That is it. The code should then always kick in when you select the Naughty Nurse Sheet

    Note: It is important that the Naughty Nurse names in column 1 of first sheet are spelt exactly the same as in their respective Tabs

    Please Login or Register  to view this content.



    ....................
    Alan
    Last edited by Doc.AElstein; 08-16-2015 at 07:59 AM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by Doc.AElstein View Post
    .. Do you know if there is a way to get at / access the tab name from a formula?????
    (shouldn't do this, but...)
    =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: copy cell value based on another cell value in range

    Additional formulas:
    Please Login or Register  to view this content.
    Additional information courtesy of Chip Pearson:
    http://www.cpearson.com/excel/FileFolderNames.aspx

    Lewis

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by FDibbins View Post
    (shouldn't do this, but...)
    =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
    Nice ..
    And it works in your formula too

    =WENNFEHLER(INDEX('Sheet1 (2)'!$A$2:$T$40;KKLEINSTE(WENN('Sheet1 (2)'!$A$2:$A$40=(TEIL(ZELLE("filename";A1);FINDEN("]";ZELLE("filename";A1))+1;255));ZEILE('Sheet1 (2)'!$A$2:$A$40)-1);ZEILEN($A$1:A1));VERGLEICH(B$1;'Sheet1 (2)'!$A$1:$T$1;0));"")

    =IFERROR(INDEX('Sheet1 (2)'!$A$2:$T$40,SMALL(IF('Sheet1 (2)'!$A$2:$A$40=(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)),ROW('Sheet1 (2)'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Sheet1 (2)'!$A$1:$T$1,0)),"")

    ... Makes a great Formula even better!
    Alan

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by LJMetzger View Post
    ........
    Sheet Name: =RIGHT(CELL("FILENAME",A1),LEN(CELL("FILENAME",A1))-FIND("]",CELL("FILENAME",A1),1))
    ......
    Also nice, and again works nicely in the main formula:

    =WENNFEHLER(INDEX('Sheet1 (2)'!$A$2:$T$40;KKLEINSTE(WENN('Sheet1 (2)'!$A$2:$A$40=(RECHTS(ZELLE("filename";A1);LÄNGE(ZELLE("filename";A1))-FINDEN("]";ZELLE("filename";A1);1)));ZEILE('Sheet1 (2)'!$A$2:$A$40)-1);ZEILEN($A$1:A1));VERGLEICH(B$1;'Sheet1 (2)'!$A$1:$T$1;0));"")

    =IFERROR(INDEX('Sheet1 (2)'!$A$2:$T$40,SMALL(IF('Sheet1 (2)'!$A$2:$A$40=(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))),ROW('Sheet1 (2)'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Sheet1 (2)'!$A$1:$T$1,0)),"")

    ... I guess the key to yours and FDibbins formula for getting at the sheet name is the
    CELL("filename",A1)
    .. should have guessed that function was there... I am learning that almost everything is there, somewhere.. Problem VBA possibilities is so vast and often so badly documented that it is difficult to find.
    . Great that you guys share your Knowledge!

    Alan

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: copy cell value based on another cell value in range

    If that was going to be used in a range of cells/formulas, I would put the tab name formula in it's own cell, then reference it

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by FDibbins View Post
    If that was going to be used in a range of cells/formulas, I would put the tab name formula in it's own cell, then reference it
    Ahh, yes makes sense as this works

    =WENNFEHLER(INDEX('Sheet1 (2)'!$A$2:$T$40;KKLEINSTE(WENN('Sheet1 (2)'!$A$2:$A$40=(RECHTS(ZELLE("filename";$A$1);LÄNGE(ZELLE("filename";$A$1))-FINDEN("]";ZELLE("filename";$A$1);1)));ZEILE('Sheet1 (2)'!$A$2:$A$40)-1);ZEILEN($A$1:A1));VERGLEICH(B$1;'Sheet1 (2)'!$A$1:$T$1;0));"")

    with A1 changed to $A$1, so is sensible to tidy the formula up by just accessing a cell with the tab name formula in it.
    . Sorry I am a bit slow at getting the point
    Alan

    ( Althogh sometimes getting the extra point across to an OP can be tricky , and one formula, even if longer than necerssary can be the better alternative.. )

  11. #11
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    SO sorry about the late reply. Things have been crazy for me. Fdibbins thank you so much. That one formula eliminates the need for several more. I was just hoping to import info not have a formula so concise and thorough. I think i am going to make a drop down list of names for the nurses. This will be on the top page. This way I can reference the location and not the specific name. You were right when you inferred the potential for error and redundancy. I am also thinking about making a drop down list of task errors. I will post what I have so far later tonight when i get to work. Again, your formula is top shelf stuff. I tried to keep up with you and Doc, but about halfway through the thread you totally lost me.

    Doc.AEIstein you are too smart for your own good (zu klug, zu deinem Besten). Vielen Dank für Ihr VBA-Code , aber ich , dass es so weit über meinem Kopf , wie die Wolken. Ich weiß nichts über VBA . Absolut nichts. Ich versuche, einen Hochschulklasseauf ihm finden . Denn jetzt muss ich nur Excel-Formel zu verwenden. Ich bin nur eine Krankenschwester und haben keine formale Ausbildung in excel hatte.

  12. #12
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    OK so I took some advice and did what I could. Now I have created a drop down list of nurse names, and it is on the last tab. My concern is that due to the number of names it will be cumbersome to work with. I cannot figure out how to make the drop down list open up larger than 10-15 names. I think it would be fine to use the drop down list for nurses names if I could get the drop down to be larger and show 25-30 names or so. If I can get that figured out then I will also make the tasks into a drop down list and just make a page on top of the Master Sheet into a user form. That is where the problem comes in. Once I create a user form can I get it to post onto the Master Sheet so the slick formula will carry it to the respective pages? Here is what I have edited the book to now. naughty list form example specific nurse2.xlsx

  13. #13
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by FDibbins View Post
    (shouldn't do this, but...)
    =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
    "Shouldn't do this"

    Not sure what that means?

  14. #14
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by Doc.AElstein View Post
    Nice ..
    And it works in your formula too

    =WENNFEHLER(INDEX('Sheet1 (2)'!$A$2:$T$40;KKLEINSTE(WENN('Sheet1 (2)'!$A$2:$A$40=(TEIL(ZELLE("filename";A1);FINDEN("]";ZELLE("filename";A1))+1;255));ZEILE('Sheet1 (2)'!$A$2:$A$40)-1);ZEILEN($A$1:A1));VERGLEICH(B$1;'Sheet1 (2)'!$A$1:$T$1;0));"")

    =IFERROR(INDEX('Sheet1 (2)'!$A$2:$T$40,SMALL(IF('Sheet1 (2)'!$A$2:$A$40=(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)),ROW('Sheet1 (2)'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Sheet1 (2)'!$A$1:$T$1,0)),"")

    ... Makes a great Formula even better!
    Alan
    I was just playing around with this improved formula, but I have no idea what it is supposed to do. When I enter it into a cell I get a file dialogue "updating file name". I was hoping you could explain what this file is doing. I am trying to get the nurse names to be a reference rather than an entry, and I guess I thought this was supposed to be a step in that direction.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Hi. Opusarlo,
    Vielen danke das sie mir in Deutsch geschrieben haben. ( Actually your German is better than mine – I’m English, just living in Germany as My Wife is German )
    . One quick point
    Quote Originally Posted by opusarlo View Post
    ..... I tried to keep up with you and Doc, but about halfway through the thread you totally lost me......
    .. we were actually doing nothing more than discussing an additional formula, to be used alone or within the initial main formula from Fdibbins that would actually get the nurse name from the name written in the tab rather than writing the actual name in the formula. So then You could use the same formula in each sheet. Otherwise You would need to modify each formula writing in the specific name of the nurse before copying the formula to a sheet. Very sorry if we confused you there . It was a very minor point. If you are happy to write the specific nurse name in each formula before pasting it in then you can forget all that and
    Quote Originally Posted by opusarlo View Post
    "Shouldn't do this"
    Not sure what that means?
    .. Fdibbins probably just meant it is bad practice, a bit messy or whatever – I expect he will clarify that later when he is “on” line.
    . As for the Code VBA alternative: I actually use code as I find it 100 times easier! . I can write codes like the one I did for you as fast as i can type. It would take me a hard long day to work through that formula from Fdibbins and understand it. It would take me a week to come up with one like it
    . In my code I write in ( almost plain ) English in those green comments exactly what each code line does. I would need a to write several pages of text to pull that formula apart and explain what is going on. Many profis who give such formulas say they do not understand them – They simply have an arsenal of such formulas which they have collected over the years. There are very few profis who can break down their formulas clearly explaining how they work. I can explain fairly easily how all the codes i write work.
    .
    . EDIT: you are very active just now! – I just saw this
    Quote Originally Posted by opusarlo View Post
    I was just playing around with this improved formula, but I have no idea what it is supposed to do. When I enter it into a cell I get a file dialogue "updating file name". I was hoping you could explain what this file is doing. I am trying to get the nurse names to be a reference rather than an entry, and I guess I thought this was supposed to be a step in that direction.
    ... Hopefully what I had already just wrote clears that up. !! I can confirm that the formula put inside the main formula does work, - I tried in the first sample file you sent. But as i mentioned if it is making it too complicated then do not use it, just type in the name of each Nurse and keep the formula simple for now.

    Alan

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Hi. Opusarlo,
    .. I am sorry but I expect I cannot help you too much further, I am just reading up now all that you have written...... I am struggling to keep up a bit, for example..
    . In Post #11 you wrote ( speaking to Fdibbins I think )
    Quote Originally Posted by opusarlo View Post
    ...... You were right when you inferred the potential for error and redundancy. ......
    .. I have no idea what you are referencing there – I saw no mention form Fdibbins anywhere about the potential for error and redundancy – have you had contact outside this Thread with him??
    .......
    . I expect actually your Excel knowledge is much more than mine: ( My entire knowledge of Excel comes from participating in Forums like these for about a year now ) . I have never worked with drop down lists and do not quite understand what you are now trying to do. As I mentioned I find the code writing the easier alternative ( Once you have learnt the basics of VBA )
    ......

    . Hopefully Fdibbins or someone will reply later and help you further. I shall follow with interest as i am curious as to exactly what you are attempting to do.
    . BTW. The particular code I did is a sort of a “based on Events” type code as they say. With such a code you do not have to actually do anything or understand anything about VBA and macros, as the code springs in automatically and does everything when you select the sheet. I would be happy to peruse the code alternative if you wish as an alternative. But maybe you are wise not to want to use something which you do not understand.

    . Good luck with your project.

    Alan

    P.s. I just uploaded your latest file from Post # 12. I see you have pasted in the formula to include the extra part to get the Name from the tab name. They all appear to work fine. I do not get the error you described in Post # 14 ???
    . ( I also pasted my code in as described in Post # 4 and it works fine as well . Just for interest I will drop the file off to you. Maybe if you get a chance you might want to try it out. As i mentioned you do not actually have to do anything. Every time you open a sheet the code kicks in and updates that sheet. NOTE: As is normal with any File with macros in, a warning will come up when you open it. You will need to select something like “allow macros” or “activate macros” in order for any code to work. I have only put the code in one Sheet module “Peter” to demonstrate. But you can copy the code to all the other sheet Modules if you wish. To do that follow my instructions given in Post # 4 ( Instruction .8) is no longer relevant – you simply save the file normally as it is already of the type that supports macros ) )
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    I am going to mark this as solved. Thanks to all of you. I think I have this aspect licked, but I have a new problem...so there is a new post.

  18. #18
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by Doc.AElstein View Post
    Hi. Opusarlo,
    .. I am sorry but I expect I cannot help you too much further, I am just reading up now all that you have written...... I am struggling to keep up a bit, for example..
    . In Post #11 you wrote ( speaking to Fdibbins I think ) .. I have no idea what you are referencing there – I saw no mention form Fdibbins anywhere about the potential for error and redundancy – have you had contact outside this Thread with him??
    .......
    . I expect actually your Excel knowledge is much more than mine: ( My entire knowledge of Excel comes from participating in Forums like these for about a year now ) . I have never worked with drop down lists and do not quite understand what you are now trying to do. As I mentioned I find the code writing the easier alternative ( Once you have learnt the basics of VBA )
    ......

    . Hopefully Fdibbins or someone will reply later and help you further. I shall follow with interest as i am curious as to exactly what you are attempting to do.
    . BTW. The particular code I did is a sort of a “based on Events” type code as they say. With such a code you do not have to actually do anything or understand anything about VBA and macros, as the code springs in automatically and does everything when you select the sheet. I would be happy to peruse the code alternative if you wish as an alternative. But maybe you are wise not to want to use something which you do not understand.

    . Good luck with your project.

    Alan

    P.s. I just uploaded your latest file from Post # 12. I see you have pasted in the formula to include the extra part to get the Name from the tab name. They all appear to work fine. I do not get the error you described in Post # 14 ???
    . ( I also pasted my code in as described in Post # 4 and it works fine as well . Just for interest I will drop the file off to you. Maybe if you get a chance you might want to try it out. As i mentioned you do not actually have to do anything. Every time you open a sheet the code kicks in and updates that sheet. NOTE: As is normal with any File with macros in, a warning will come up when you open it. You will need to select something like “allow macros” or “activate macros” in order for any code to work. I have only put the code in one Sheet module “Peter” to demonstrate. But you can copy the code to all the other sheet Modules if you wish. To do that follow my instructions given in Post # 4 ( Instruction .8) is no longer relevant – you simply save the file normally as it is already of the type that supports macros ) )
    The inferenc3e of error and redundancy was just an inference...not specifically mentioned. I believe this is what he was referencing when he suggested I make the nurse names into a list that can be reference rather than manually enter them...I have 40+ nurses to make sheets on so it really made sense when he told me that.. The only thing is I am only about 70% clear on how to do that, which is why I made my lists page. Thank you for all your help. I really appreciate it.

  19. #19
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Hi. Opusarlo,
    Quote Originally Posted by opusarlo View Post
    .. Thank you for all your help inferenc3e of error and redundancy was just an inference. ..
    . Your welcome, thanks for the feedback and clearing that up.
    ...............................................

    Quote Originally Posted by opusarlo View Post
    ..... The only thing is I am only about 70% clear on how to do that, which is why I made my lists page.......
    ... Just to clear that up then
    . 1) This is just a Small point I just noticed: - a small Typo in Fdibbins Formual ( a missing second bracket at the end ). But that should not have thrown you off as Excel ( at least mine ) auto corrected it when I pasted the formula in

    . 2 ) The main point of what we were saying was simply that:
    . - The main formula gets unnecessarily big when you include the bit to get the name from the tab. So what Fdibbins rightly suggested it would be more wise and tidy to do the following:

    (i) First put that formula....
    .- Filename Formula:
    =(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
    ......in some arbitrary spare cell, let’s say in Q1
    Using Excel 2007
    Row\Col
    Q
    1
    =(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
    Peter


    (ii) Now, for example, change your formula in the Peter sheet ( cell B2 ) from this...

    . - Formula with an actual name in:
    =IFERROR(INDEX('Master Sheet'!$A$2:$T$40,SMALL(IF('Master Sheet'!$A$2:$A$40="Peter",ROW('Master Sheet'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Master Sheet'!$A$1:$T$1,0)),"")
    Using Excel 2007
    Row\Col
    B
    2
    =IFERROR(INDEX('Master Sheet'!$A$2:$T$40,SMALL(IF('Master Sheet'!$A$2:$A$40="Peter",ROW('Master Sheet'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Master Sheet'!$A$1:$T$1,0)),"")
    Peter


    ...To

    . – Formula accessing the name which is given by the formula in Q1
    =IFERROR(INDEX('Master Sheet'!$A$2:$T$40,SMALL(IF('Master Sheet'!$A$2:$A$40=$Q$1,ROW('Master Sheet'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Master Sheet'!$A$1:$T$1,0)),"")
    Using Excel 2007
    Row\Col
    B
    2
    =IFERROR(INDEX('Master Sheet'!$A$2:$T$40,SMALL(IF('Master Sheet'!$A$2:$A$40=$Q$1,ROW('Master Sheet'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Master Sheet'!$A$1:$T$1,0)),"")
    Peter
    ( The only difference is that "Peter" has been changed to the ( non changing, hence the $ s ) to the referrence to cell Q1

    . The point we were making is that the above last formula is ( a little ) tidier than the full formula

    . – Complete “one off” Formula.
    =IFERROR(INDEX('Master Sheet'!$A$2:$T$40,SMALL(IF('Master Sheet'!$A$2:$A$40=(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)),ROW('Master Sheet'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Master Sheet'!$A$1:$T$1,0)),"")
    Using Excel 2007
    Row\Col
    B
    2
    =IFERROR(INDEX('Master Sheet'!$A$2:$T$40,SMALL(IF('Master Sheet'!$A$2:$A$40=(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)),ROW('Master Sheet'!$A$2:$A$40)-1),ROWS($A$1:A1)),MATCH(B$1,'Master Sheet'!$A$1:$T$1,0)),"")
    Peter

    (iii) Repeat the above two steps for in all name sheets
    ...
    . So there is no need for you to make any extra lists of names anywhere.
    .....................
    . You have proved my point that, As i mentioned in my post #10, getting that all across is a bit difficult, and why I suggested staying with the really big formula with everything in it. But your choice. Use whatever you feel comfortable with. But again, there is no need for creating an extra name list. Nothing I or Fdibbins said was supposed to infer that.

    Hope that helps. It is a very simple point once you know, but like everything a bit difficult to get across to anyone the first time. That is why i suggested the full one off formula as easier to get acros to you initially. ( Possibly the typo throw you off, but as I said by me it auto corrected and I only just noticed it as I prepared this all for you )

    Alan

    P.s. When you reply with a quote to a Post, please edit out most of the text, only leaving in the relavent parts: This makes the Thread less cluttered and easier to follow
    Thanks, Danke!
    Last edited by Doc.AElstein; 08-20-2015 at 09:56 AM.

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by opusarlo View Post
    "Shouldn't do this"

    Not sure what that means?
    Some may consider Alan's question "asking a question on another member's thread", but it was (I saw later) kind of related to this question

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by FDibbins View Post
    Some may consider Alan's question "asking a question on another member's thread", but it was (I saw later) kind of related to this question
    ... and IM very humble O added to and helped initiate a very interesting and informative Thread..
    Thanks again for the enhancing input
    Alan

    P.s.
    The OP has continued here:
    http://www.excelforum.com/excel-gene...ml#post4165517

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: copy cell value based on another cell value in range

    Yes it did, made for a potentially more efficient process )

  23. #23
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: copy cell value based on another cell value in range

    Quote Originally Posted by Doc.AElstein View Post
    Hope that helps. It is a very simple point once you know, but like everything a bit difficult to get across to anyone the first time. That is why i suggested the full one off formula as easier to get acros to you initially. ( Possibly the typo throw you off, but as I said by me it auto corrected and I only just noticed it as I prepared this all for you )

    Alan

    Hey Alan,

    Not sure if anyone has ever told you this, but you should be an educator. Your descriptions made the formulae crystal clear to me. Thank you so much for taking the time to explain it to me. As an avid fan of Excel just learning how to use it I am eager to gather all the knowledge I can. Going to copy this to your inbox as well just in case you do not get back to this thread...it is a bit old after all.

    Arlo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy range based on cell value
    By jumonjii in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2014, 10:20 PM
  2. How to copy a cell range based on an individual cell
    By Merlin_Stryker in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-15-2014, 01:18 PM
  3. Copy cell range to another workbook based on a value
    By Sassyponypants in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2013, 09:37 AM
  4. [SOLVED] Copy data from a range of cells into a blank range based on common cell
    By vanmeterkj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-03-2012, 10:18 AM
  5. How to copy a cell range based on a condition
    By GregoryA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2012, 01:42 AM
  6. Macro to copy range of cell from sheet1 to sheet2 based on a cell value in sheet2
    By drgwhizz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-25-2012, 10:39 AM
  7. Copy a range based on a cell value
    By tuke016 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2011, 10:46 AM

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