Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Create a Lookup over multiple worksheets

  1. #1
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007

    Create a Lookup over multiple worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have an Excel spreadsheet that I use to record my coin collection. I have one sheet as a home page, all other sheets (over 20) are named for the denomination of the coin, i.e. silver 3d, silver shilling etc. On each of those sheets are consecutive lists of years using Column A, Column B will indicate if I have the coin. I want to set up a lookup query on the home page that will search through a named sheet and take me to a specific year within that sheet. For example, Whilst on my homepage I want to see if I have a silver shilling dated 1895 without having to manually select the specific worksheet tab and then scroll through the sheet to that year. Is this possible? Thanks.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    Welcome to the forum.

    Are you really using Excel 2007?

    Please attach the workbook using the attachment facility - click on Go Advanced bottom right of your post and scroll to Manage Attachments.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007
    Hi, yes, I know it's an old version but I've never had the need to upgrade it. Hopefully the file is attached for you,you need to click on the single image when workbook opens. You'll see that it does contain Macros so that I can quickly move about where I need to, thanks
    Attached Files Attached Files
    Last edited by AliGW; 2019-12-22 at 08:53 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    I do not see where you have manually mocked up what you are trying to achieve. All I see on the 'home' page (coin totals?) are the totals.

    PS To achieve what you want, you'll need to have separate sheets for crowns and half crowns.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007
    Hi, you're correct on the Homepage........no there isn't a mockup there...........I really didn't know where to start.

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    So explain how you want it to work. Are you envisaging having a drop-down to select a type of coin and a drop-down to select the year? If so, that's easily done, but all sheets must have the same format, hence my advice above about crowns and half crowns. You will also need to create two lookup lists for the drop-downs (these can be on a hidden sheet).
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007
    Hi, a drop down list sounds good. The Crowns are on the same sheet as the half crowns as there aren't that many of them, so i can easily check them, but I can set up another sheet if that's going to make it work easier. I do appreciate your help, thank you......it's been several years since I did any serious work on spreadsheets and I guess I'm a bit out of practice

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    You will need to standardise the layout: on some sheets you have a type column, others not. In the attached, I have separated out the crowns and half crowns - you'll need to do the same for English and Scottish shillings and then adjust tab names and lookup lists accordingly.

    I have created two lookup lists and these are named ranges: Coins and Years.

    The drop-down selectors are on the coin totals sheet.

    The formula used for the search is:

    =IFNA(VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0),"No")
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Seeker Jester's Avatar
    Join Date
    Dec 2019
    Posts
    7
    Articles
    0
    Excel Version
    2007
    Thanks Ali, have just got back in after Xmas visiting, so will have a look at this during the afternoon :-) Glenn

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    OK - let us know how you get on.
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •