Multiple sheets information linked to one sheet [SOLVED]

  • I'm trying to link info from several sheets into one sheet.


    I know I can just put the formula "=sheet! cell " into each cell I need it to link to, but I'm trying to drag the formula down a column and the sheets are not in the same format.



    Any way I can establish the formula at the top, then drag it down the column


    EX


    What it's doing is


    Sheet1 cell1
    Sheet1 cell2
    Sheet1 cell 3



    I need it to do


    Sheet1 cell1
    Sheet2 cell1
    sheet3 cell1



    Any help is appreciated. :D

  • Hi Driver,


    One way would be to use the following formula in cell B1 and drag down for the appropriate number of sheets.


    =INDIRECT(CONCATENATE("Sheet",ROW(),"!A1"))


    where,


    =ROW() return the row for the cell,
    =CONCATENATE() is used to build a textual definition of the cell you are linking to,
    =INDIRECT() takes the text value and uses it as a range reference.


    Hope this helps.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Thanks for the reply. I feel like a noob asking this question, but


    I'm using INDIRECT(CONCATENATE("Sheet2",ROW(6),"!B6"))



    Trying to paste data from sheet 2 b6 into


    sheet 1 b3


    this of course is not working. Would you please explain this a little more. Thank you

  • Hi Driver,


    =INDIRECT(CONCATENATE("Sheet2",ROW(6),"!B6"))


    will fail because if ROW() has an argument it needs to be a valid reference.


    so ROW(B6) would return 6


    You formula, after fixing ROW(), would build a reference of
    Sheet26!B6


    Try,
    =INDIRECT(CONCATENATE("Sheet",ROW(A2),"!B6"))


    which should return Sheet2!B6


    Does this make it clearer :question:


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Hi Driver,


    Yes that would be true.


    But now all the pieces of the formula are text. If you drag the formula down a couple of cells nothing will change!


    Your original question wanted the sheet name to change whilst you copied the formula to some adjacent cells.


    What should BAKER change too ??


    Maybe you could post a example file with the desired results done manually and we can then create a useful formula.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!