+ Reply to Thread
Results 1 to 7 of 7

Function to extract certain cells from multiple sheets

  1. #1
    Forum Contributor
    Join Date
    05-24-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    345

    Function to extract certain cells from multiple sheets

    Hi,

    I have 4 spreadsheets.

    I need to extract the values in cells

    b18
    b38
    b58
    b78
    b98
    b118
    b138
    b158
    b178
    b198
    b218
    b238

    In each of these spreadsheets and post them in a column in a new spreadsheet.

    I was hoping possibly someone could help with this without having to use a macro and I thought the index function might make this possible?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index function help please

    For only 4 sheets and 12 cells, this might one of the simplest ways...

    enter =INDEX(Sheet2!$B$18:$B$238,20*(ROWS($B$1:B1)-1)+1)

    changing Sheet2 to first sheet name, copy down 12 cells, then enter the formula again in the 13th cell and sheet sheetname again to next sheet, copy down 12 cells and repeat twice more.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    05-24-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    345

    Re: Index function help please

    Thanks mate.

    Just for my learning, could you please just walk through the syntax in layman language please?

    Thanks again

  4. #4
    Forum Contributor
    Join Date
    05-24-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    345

    Re: Index function help please

    Ah, one slight problem, when I go into 13th cell and try and change the formula, it directs me to "update values ) dialogue box and won't let me type anything in
    Last edited by floricita; 08-22-2011 at 10:26 AM. Reason: change

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index function help please

    So you copied my formula above again into cell 13 and tried to change Sheetname?

    Is it spelled correctly?

    Also, if sheet name is 2 words, add apostrophes,

    e.g.

    =INDEX('Sheet 2'!$B$18:$B$238,20*(ROWS($B$1:B1)-1)+1)

  6. #6
    Forum Contributor
    Join Date
    05-24-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    345

    Re: Index function help please

    Yes I did all that, and the error comes up. The way I have got around it is by doing it in another column.then copy and paste below the other set.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index function help please

    When you get the Update Values box it means usually that you the sheet name you are referencing isn't existing... maybe due to mispelling or maybe you need to enclose sheetname in apostrophes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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