displaying values from a number of worksheets

  • I have a text in sheet2!A1, sheet3!A1,...sheet20!A1 and I want to display those values in sheet1!B1:B20. To automate the work, I put the text 'sheet1' in A1, 'sheet2' in A2....'sheet20' in A20.
    What formula should be put in B1 and copy down to display the values from those worksheets?
    Can I use INDIRECT()?
    Thanks for your help

  • Re: displaying values from a number of worksheets


    Try:
    [COLOR="#0000FF"]
    =INDIRECT("'"&A1&"'!A1")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: displaying values from a number of worksheets


    Displaying values from different worksheets



    Further to previous post.
    I have 6 values in sheet2, 2 values in sheet3, 10 values in sheet4. To display those values in sheet1, I can copy those values manually but I want to automate this work. What formula should be put in sheet1?
    The problem is:
    the numbers of values maybe different in different worksheets, how to put them in a single column in sheet1?
    I have attached the xls for your ref. I want a formula in sheet1!A5 and copy down.
    Thanks for your help

    Files

    • sample.xlsx

      (11.07 kB, downloaded 83 times, last: )