Create a Lookup over multiple worksheets

Jester

New member
Joined
Dec 22, 2019
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2007
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.
 
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.
 
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
 

Attachments

  • Updated Coins.xlsm
    147.9 KB · Views: 11
Last edited by a moderator:
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.
 
Hi, you're correct on the Homepage........no there isn't a mockup there...........I really didn't know where to start.
 
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).
 
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
 
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")
 

Attachments

  • Coin Database AliGW.xlsm
    181.8 KB · Views: 11
Thanks Ali, have just got back in after Xmas visiting, so will have a look at this during the afternoon :) Glenn
 
Hi, the formula in the spreadsheet that you've attached reads;

=_xlfn.IFNA(VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0),"No")

No matter what data I select from the 2 drop down boxes you've created, the result reads '#NAME?' which suggests an error in the formula - but having never used IFNA or INDIRECT, I'm not sure where I should be looking to put in any amendments. I tried it first on the Crown and Half Crown tabs you've done and then on other tabs, but the result is always the same. Yours - Baffled and Puzzled, Glenn
 
It will be the IFNA function - sorry.

Try this:

=IF(ISNA(VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0)),"No",VLOOKUP(F22,INDIRECT("'"&E22&"'!$A$4:$B$1200"),2,0))
 
Brilliant! That's what I need it to do, thank you so much. I've been googling the indirect and ifna functions to try and get a better understanding and will now save this spreadsheet in 2 places so I've got a backup if I mess things up. Now off to play a bit more. Merry Christmas, kind regards, Glenn
 
Back
Top