Thomas Zucker-Scharff
asked on
Excel O365 VBA to query another tab's info
I did a search on EE and found this comment (https://www.experts-exchange.com/questions/29044535/Excel-VBA-search-userform.html?anchorAnswerId=42219448#a42219448) in an answer to a question. It almost works for me, but not quite And I am not that good with VBA to make the alterations.
I am attempting to do the following:
Search a tab called "2019-2020 LT Members Table" - in the same worksheet - for the currently selected value in the tab I am working on and return the value in the "Primary Last Name" column and the value in the "BH Member" column from the other tab (checking to see if BH Member is equal to 1).
Due to the nature of the spreadsheet, I am not at liberty to post the working copy. I am attaching a version with the data taken out.
LT-Full-membership-list-blank.xlsm
I am attempting to do the following:
Search a tab called "2019-2020 LT Members Table" - in the same worksheet - for the currently selected value in the tab I am working on and return the value in the "Primary Last Name" column and the value in the "BH Member" column from the other tab (checking to see if BH Member is equal to 1).
Due to the nature of the spreadsheet, I am not at liberty to post the working copy. I am attaching a version with the data taken out.
LT-Full-membership-list-blank.xlsm
ASKER
sorry use 4288
Do you even need VBA? sounds very much like a VLOOKUP?
ASKER
I don't know what I need actually. I just want to accomplish the task. I have migrated the excel spreadsheet to an Access database, so the question is more for my own edification now.
I am not an expert with Excel, although I am fairly good at it.
I am not an expert with Excel, although I am fairly good at it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scott,
I marked this answered, but I just tried the code and it didn't work. Is there different code if I am comparing two tables and preferably concatenating the first and last names? In the attached, I want to be able to enter a name in one table on sheet 2 and have it check another tab's (sheet 1) table for the matching name and return whether that person has LTB member checked or not. They could be anywhere in the other table's entries, but the name will be in the same column. There may be duplicate names but the first name will be different if the last name is the same. Ther may also be duplicate entries for the same person on sheet 2, but not on sheet 1.
Copy of zucker.xlsx
I marked this answered, but I just tried the code and it didn't work. Is there different code if I am comparing two tables and preferably concatenating the first and last names? In the attached, I want to be able to enter a name in one table on sheet 2 and have it check another tab's (sheet 1) table for the matching name and return whether that person has LTB member checked or not. They could be anywhere in the other table's entries, but the name will be in the same column. There may be duplicate names but the first name will be different if the last name is the same. Ther may also be duplicate entries for the same person on sheet 2, but not on sheet 1.
Copy of zucker.xlsx
Sure, I will look at your workbook.
Tom,
To keep things simple, I made 2 new sheets, 1B and 2B without any fieldnames. Let's get this to work first, I ran into an issue too with your data and rather than figuring it out, I just like to simplify, then add one.
Also, you didn't have any matching name on sheet 1 and 2. You will see highlighted I have jane name 9 in both. Also, I am just doing the vlookup at this point. You can always add the if(vlookup stuff =1,"yes","no"). For now, just look at what is being produced. Notice that o Sheet2B in Column J, rows 2 through 14 are "#N/A" and this represents nothing found. With this in mind, you will also want to use ISERROR https://support.office.com/en-us/article/is-functions-0f2d7971-6019-40a0-a171-f2d869135665 and if there is an error, either also set it to "No" or perhaps, "Not Found", whatever you choose, just make sure to account for it.
Another issue is the vlookup works best when there is one unique value. In the example I uploaded, "jane name9" has both a 0 and 1 but the 0 is in a row above the 1 and that makes all the subsequent lookups set to the first occurrence which is 0.
Depending on what you want to do, you may want to get into some vba to better account for this.
Copy-of-zucker.xlsx
To keep things simple, I made 2 new sheets, 1B and 2B without any fieldnames. Let's get this to work first, I ran into an issue too with your data and rather than figuring it out, I just like to simplify, then add one.
Also, you didn't have any matching name on sheet 1 and 2. You will see highlighted I have jane name 9 in both. Also, I am just doing the vlookup at this point. You can always add the if(vlookup stuff =1,"yes","no"). For now, just look at what is being produced. Notice that o Sheet2B in Column J, rows 2 through 14 are "#N/A" and this represents nothing found. With this in mind, you will also want to use ISERROR https://support.office.com/en-us/article/is-functions-0f2d7971-6019-40a0-a171-f2d869135665 and if there is an error, either also set it to "No" or perhaps, "Not Found", whatever you choose, just make sure to account for it.
Another issue is the vlookup works best when there is one unique value. In the example I uploaded, "jane name9" has both a 0 and 1 but the 0 is in a row above the 1 and that makes all the subsequent lookups set to the first occurrence which is 0.
Depending on what you want to do, you may want to get into some vba to better account for this.
Copy-of-zucker.xlsx
ASKER
Scott,
I am obviously doing something wrong. I input the formula and each and every entry came out as NA. I did the following
Created a concatenate column in both spreadsheet tabs (column2 - actual column I or 9th column - in the table doing the lookup and row AH in the table housing the name and address info - 2019-2020 LT Members Table)
use vlookup to compare the first concatenated name to the table with several hundred unique names
Much of this has become academic since I converted the spreadsheet to an Access database (a mistake to use Access - I think, or maybe I am just not that good an access programmer).
I am obviously doing something wrong. I input the formula and each and every entry came out as NA. I did the following
Created a concatenate column in both spreadsheet tabs (column2 - actual column I or 9th column - in the table doing the lookup and row AH in the table housing the name and address info - 2019-2020 LT Members Table)
use vlookup to compare the first concatenated name to the table with several hundred unique names
=VLOOKUP([@Column2],'2019-2020 LT Members Table'!A:AH,9,FALSE)
LTB membership data (1 or Null) is in column A in the 2019-2020 LT Members Table tab.Much of this has become academic since I converted the spreadsheet to an Access database (a mistake to use Access - I think, or maybe I am just not that good an access programmer).
The NA's are most likely because of no data. What you want to do is really better suited for your Access (or any) database though.
ASKER
Scott,
Yeah, that is why I ended up porting it. Thanks for the help!
Yeah, that is why I ended up porting it. Thanks for the help!
»bp