+ Reply to Thread
Results 1 to 7 of 7

Lookup in specific sheet name and extract values

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    na
    MS-Off Ver
    2010
    Posts
    24

    Lookup in specific sheet name and extract values

    Hi All

    Wanted to reach out to the experts for this. Please see attached document. This workbook is used to track upcoming sales roadshows (which last usually one week). I want to create a summary/meeting page whereby I can see how many meetings have been booked for each sales roadshow.

    I've created this on the meeting count tab. You can see that i've used formula to get all the tab names into a table format.

    The part i'm stuck on is how can i populate column C, D, E using formula. The numbers I need are highlighted green on all the subsequent tabs. I want to use formula rather then manually adding numbers because we are constantly adding new tabs and also new meetings and so I would want that to automatically update the summary table.

    Apologies for the crappy explanation, let me know if you need more details.

    Thanks,

    Ahmed
    Attached Files Attached Files
    Last edited by AK123; 05-01-2019 at 07:00 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Lookup in specific sheet name and extract values

    No document attached

    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-25-2015
    Location
    na
    MS-Off Ver
    2010
    Posts
    24

    Re: Lookup in specific sheet name and extract values

    Done and thank you for the instructions!

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Lookup in specific sheet name and extract values

    Just to add this looks extremely complicated and I don't have much time to solve these problems so I'm gonna have to pass on a solution.

  5. #5
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Lookup in specific sheet name and extract values

    Hi AK, I think this is what you're after, you're not after a sum of all the worksheets but referencing only the specific worksheet referenced in the B column.
    I've referenced the worksheet with the Indirect function. Hopefully I've understood you're problem properly.

    If you were after summing all the values in the same cell over all sheets look into 3d References. Also you could make it more dynamic by entering a hidden worksheet at the start & end & referencing that worksheet. I'll stop there as that's another topic which I don't think you're after in this situation.

    CAUTION: I noticed you had general data type to the values you were summing; which excel could probably convert these values to numerical value with no real problem, however it's better practice to have numbers as number data types.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-25-2015
    Location
    na
    MS-Off Ver
    2010
    Posts
    24

    Re: Lookup in specific sheet name and extract values

    Dear Dal - this is exactly what i was after! Thank you so much. I can't say I've ever used the indirect function before. Just to confirm, this is looking up the sheet name and then showing what ever is in cell K2 and L2.

    Noted on the data type I will ensure I use numbers. Thank you for solving!

  7. #7
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Lookup in specific sheet name and extract values

    You're very welcome AK, yes you're correct! If you look at the formulas you'll notice the & operator; followed by 2 double quotes. Whatever is in them quotes is concatenated in the formulas.
    Basically a fancy way of saying it adds the neccessary grammar to your values in the left to look up the worksheet.
    If it's been helpful please mark as helpful

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. extract specific values
    By keshavtale in forum Excel General
    Replies: 2
    Last Post: 08-21-2015, 04:59 AM
  2. [SOLVED] Vlookup in UserForm - Get Input from listbox, Lookup in specific table from specific sheet
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-30-2013, 09:00 AM
  3. Extract specific column data from specific sheet from multiple files in a folder
    By piggyfox in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-31-2013, 11:51 AM
  4. Macro to extract data from multiple workbooks, specific sheet, specific cells
    By crissandraauree in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2012, 03:54 PM
  5. Replies: 5
    Last Post: 04-12-2011, 12:59 PM
  6. Macro Loop to Extract Specific Values and Min/Max Values From Column/Rows Range
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-19-2010, 09:19 AM
  7. extract specific values from list
    By mcm1009 in forum Excel General
    Replies: 5
    Last Post: 10-15-2010, 04:50 PM

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