get value from cell in a range
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: get value from cell in a range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default get value from cell in a range

    I have several hundred workbooks that the Identification number is in various cells between H2 and P4. In cell AI1 would like a formula that will get the Identification Number no matter what cell it is in between H2 and P4

    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: get value from cell in a range

    Hi,
    How am I supposed to identify whether the vakue in the cell is an ID number or not? Is your ID some kind of sequence number? What is the pattern of it?

  3. #3
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    Hi Mentor, it will be text string (alpha numeric))

  4. #4
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: get value from cell in a range

    Hi,
    Thanks for your reply but it does not tell me much though. Does it have a constant lenght? If so, how long is it? How many letters and cell are within the Id? Are all of your ids with the same pattern? Can you write down a example od your id? I'm asking these questions because I'm trying to finger out how I'm meant to distinguish between id an any other text/value you might have among those cells.

  5. #5
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    that will be the only text in that range the text length will vary since it will be different barge companies with different name conventions such as Barge Kirby 10000, Barge Kirby 10000B,CTCO 130 or even just number such as 303

  6. #6
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: get value from cell in a range

    Hi,
    Check iut this own-developed function GetId. Copy it.to the module of the workbook you have and try to use it, the GetId function and let me knkw if that's ok for you. The function requires one range paramater IdRange where you should provide a range of cells (H2:P4) of which one includes id you want to get.
    Code:
    Function GetId(IdRange as range) as string
       Dim rCell as range
       For each rCell in IdRange
           If isnumeric(r.Cell.value)=false then
                GetId = cstr(r.Cell.value)
                Exit for
           End if
       Next
    
    End function

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,444
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: get value from cell in a range

    Quote Originally Posted by trimmer69 View Post
    that will be the only text in that range ...
    In that case, if you have a recent version of Excel with the CONCAT function, use this in AI1

    =CONCAT(H2:P4)

    If you don't have the CONCAT function then try

    =INDEX(H2:P4,SUMPRODUCT((H2:P4<>"")*ROW(H2:P4))-ROW(2:2)+1,SUMPRODUCT((H2:P4<>"")*COLUMN(H2:P4))-COLUMN(H:H)+1)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    thanks for function, but I get error message #Value !. I ended up using the merge/unmerge for that range which moved the text into H2. Thanks for the help

    Peter_SS the concat formula gave error #Name ! and the last one gave error #Ref !

    I ended up merging the range.

    Application.DisplayAlerts = False
    Range("H2:P4").Merge
    Range("AI1").Value = Range("H2").Value 'name
    Range("H2:P4").UnMerge
    Application.DisplayAlerts = True
    Last edited by trimmer69; Aug 18th, 2019 at 03:26 PM. Reason: added more text

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,444
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: get value from cell in a range

    Quote Originally Posted by trimmer69 View Post
    Peter_SS the concat formula gave error #Name !
    I mentioned that you needed a recent Excel version to use CONCAT & that error message indicates that you version does not have that function.


    Quote Originally Posted by trimmer69 View Post
    ... and the last one gave error #Ref !
    That would indicate to me that your statement "that will be the only text in that range" is not strictly true. If it appears there is no other text in the range then perhaps you have one or more cells containing blank spaces or formula that returns " " or similar, making them appear blank to the user, but not to the formula. Out of interest, if you still have a sheet that is giving that REF error with H2:P4 unmerged, what does this formula, placed in a vacant cell, return?
    =COUNTIF(H2:P4,"?*")
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    May 2004
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: get value from cell in a range

    Peter, when I copied and pasted into cell outside of range I got 1.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •