+ Reply to Thread
Results 1 to 7 of 7

Referencing from multiple sheets back to sheet1

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    Romiley, England
    MS-Off Ver
    Excel 2000
    Posts
    19

    Referencing from multiple sheets back to sheet1

    I have many sheets generated and named from cell values in a column (Q10) on sheet1, the sheet name takes the form 000-12345ABC and each sheet increments by 5 each time so the next sheet would be 005-12345ABC, then 010-12345ABC and so on.
    I then want to copy a column of text into each of the sheets and change a few of the values in this column to match certain values in my main sheet. Writing it out longhand I suppose this is what I want:

    If the sheet name starts with 000(or 005, 010 ect) then look in sheet1, find the value 000(or 005, 010 ect) in column N and insert the value from column P from the same row.

    Any Ideas?

    Tim

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Referencing from multiple sheets back to sheet1

    How about:

    =VLOOKUP(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,3),'Sheet1'!N:P,3,FALSE)

    ?

  3. #3
    Registered User
    Join Date
    04-30-2010
    Location
    Romiley, England
    MS-Off Ver
    Excel 2000
    Posts
    19

    Re: Referencing from multiple sheets back to sheet1

    Thanks for having a look at this for me, I have been trying to break it down but I am struggling. I knew VLOOKUP would be invloved, I think that MID and 1,3 looks at the first three characters of the sheetname. With the CELL function I presume I have to input the full filename and path of the spreadsheet, after that I am getting really confused. Any chance you could break it down for me, if this works then I can apply the same routine to other cells.

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Referencing from multiple sheets back to sheet1

    No need to input the file name, simply type it in as is.

    The CELL function returns information about a cell. The "Filename" is simply the info type you're looking for, which brings back the full filename. Try it for yourself by typing in the following:

    =CELL("filename",A1)

    We then use the FIND function to look for the first ] character in the filename, which is the character before the sheet name (for instance [Book 1.xls]005-12345ABC), then the +1,3 brings back the next 3 characters.

    From that point on, it's just a VLOOKUP as usual.

    Hope this helps.

  5. #5
    Registered User
    Join Date
    04-30-2010
    Location
    Romiley, England
    MS-Off Ver
    Excel 2000
    Posts
    19

    Re: Referencing from multiple sheets back to sheet1

    I do see what you are getting at but when I run the formula I get N/A returned.

    It seems a bit cheeky but I have posted a copy of the workbook in question so you can see exactly what I am trying to do. I have already created the formulas/Macros that populate the main data in sheet 1 and creates the worksheets (so I am making steady progress) and I have also indicated on sheet 000-22762KBM in red what I am attempting to do.
    Could you have a look for me?

  6. #6
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Referencing from multiple sheets back to sheet1

    Ah, because your cyclone section values were formatted as numbers the lookup didn't match as the string we take from the MID function is a text string.

    We therefore need to use the VALUE function to turn this string into a number, et voila!

    =VLOOKUP(VALUE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,3)),Sheet1!N:P,3,FALSE)

  7. #7
    Registered User
    Join Date
    04-30-2010
    Location
    Romiley, England
    MS-Off Ver
    Excel 2000
    Posts
    19

    Re: Referencing from multiple sheets back to sheet1

    My Friend you are a star, it works a treat you can appreciate how much work that will save as I was contemplating manually filling in the data. Will look at adapting to other areas.

    Many thanks

    Tim

+ 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