index/match simple question
Thanks Thanks:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: index/match simple question

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile index/match simple question

     
    i mean, in sheet1 i have this:



    in sheet2:


    in sheet3:


    my question is:

    how can i use index/match across multiple sheets (even when new sheet is created, possible?) at sheet1 A1 (i have thousand lines in B column in reality)?

    thank you all
    Last edited by and8eye; Mar 16th, 2018 at 11:21 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: index/match simple question

    what formula are you using now? most formulas will stay across multiple sheets if additional sheets are added inbetween so you could make the last sheet not count IE nil return or zeros then insert new sheets just in front of that one
    I visit this site mainly to remember how little I know

  3. #3
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match simple question

    Quote Originally Posted by Dryver14 View Post
    what formula are you using now? most formulas will stay across multiple sheets if additional sheets are added inbetween so you could make the last sheet not count IE nil return or zeros then insert new sheets just in front of that one
    im using index/match now, but i have to put name of sheet into the formula, im thinking of indirect, but i dont know how

  4. #4
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match simple question

    please

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: index/match simple question

    I do not know a way to use indirect with this function, how many sheets are we talking for a few you could do something like this

    iferror(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0)))

    Other than that I can offer no more, sorry, sometimes saying simple question doth not a simple answer return me thinks
    I visit this site mainly to remember how little I know

  6. #6
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match simple question

    Quote Originally Posted by Dryver14 View Post
    I do not know a way to use indirect with this function, how many sheets are we talking for a few you could do something like this

    iferror(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0)))

    Other than that I can offer no more, sorry, sometimes saying simple question doth not a simple answer return me thinks
    i have somes but this way when new sheet is created, i will have to update the formula again

    or any other formula do you think its possible?

    thanks anyway tho
    Last edited by and8eye; Mar 16th, 2018 at 01:15 PM.

  7. #7
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: index/match simple question

    I dont think there is any formula that knows a new sheet has been added and to update itself.

    There may be a vba solution but I would not know how to do it sorry.

    Good luck
    I visit this site mainly to remember how little I know

  8. #8
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match simple question

    Quote Originally Posted by Dryver14 View Post
    I dont think there is any formula that knows a new sheet has been added and to update itself.

    There may be a vba solution but I would not know how to do it sorry.

    Good luck
    thank you

    btw, can you please take a look at this: https://www.mrexcel.com/forum/excel-...stion-3-a.html

  9. #9
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match simple question

    please

  10. #10
    New Member
    Join Date
    Mar 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match simple question

      
    someone please

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
  •  

 

 
DMCA.com