Hi!
Attached is a document that a few people here have helped me with! I need a bit more help.
I would like to auto populate columns B and C of worksheet16-Q3 by finding out this information:
When I type a name in column A of 16-Q3 I would like it to look at ALL worksheets for that name and if there is a 4 in that row in column J, I would like it to fill out Columns B and C of 16-Q3, with what is in in columns G and H of that same row. B=G and C=H.
I have no idea if this can be accomplished, lol, but I thank you all in advance!
Lisa :) mastercalltoEE.xlsm
Microsoft ExcelInstallation
Last Comment
Frank Helk
8/22/2022 - Mon
byundt
You can do what you want with a user-defined function and a worksheet formula like:
=vlookup3dtest($A19,'Aug ''16'!$A$1:$J$1000,7,FALSE,"Apr '16",10,4) & ""
In the second parameter, note that there are two single quotes preceding 16--not a double quote.
Install the user-defined function in a regular module, just like it was a macro.
As written, the UDF returns #N/A (as text) if it cannot find the test value (4 in your request) in the lookup sheets. Since your test case had one column that was blank and you were returning text, I appended an empty string to the value returned by Vlookup3DTest.
Function Vlookup3DTest(LookupValue As Variant, LookupTable As Range, ReturnColumn As Long, DataSorted As Boolean, LastSheetName As String, _ TestColumn As Long, TestValue As Variant) As VariantDim ws As WorksheetDim wb As WorkbookDim rg As RangeDim i As Long, FirstSheetIndex As Long, LastSheetIndex As LongDim addr As StringDim v As VariantApplication.VolatileVlookup3DTest = "#N/A"addr = LookupTable.AddressSet wb = LookupTable.Worksheet.ParentSet ws = wb.Worksheets(LastSheetName)FirstSheetIndex = LookupTable.Worksheet.IndexLastSheetIndex = ws.IndexFor i = FirstSheetIndex To LastSheetIndex Set rg = wb.Worksheets(i).Range(addr) v = Application.VLookup(LookupValue, rg, TestColumn, DataSorted) If Not IsError(v) Then If v = TestValue Then Vlookup3DTest = Application.VLookup(LookupValue, rg, ReturnColumn, DataSorted) Exit Function End If End IfNextEnd Function
Hi Byundt, but I can't open the file you attached?
Tom Farrar
I think you may have a problem when you look at all sheets as Column J is not consistent on all worksheets. Perhaps I am not understanding what you are looking for, but there is no column J in Jan-Jun.
lisafamf,
The file I posted has a .xlsm extension and corresponding file format.
When I tried to download the file from Experts-Exchange, however, I see that they added a .xlsx file extension to it. They did the same thing to the file you posted, too.
Please download the file onto your computer without trying to open it. Then rename the file to delete the .xlsx at the end of the file name. Make sure the file name ends with .xlsm and you should then be able to open the file in Excel.
I elevated the issue to Experts-Exchange technical support. After doing so, I learned they pushed a fix for the problem tonight, though it isn't working for me. FWIW, Chrome seems to download the file just fine. Both Firefox (my primary browser) and Internet Explorer have problems downloading .xlsb and .xlsm files on Experts-Exchange.
Brad
lisafamf
ASKER
Hi Tom and Byundt, thanks for your responses! I won't be able to address them until Wednesday, as I was slammed with something else. Thanks!!!
Lisa
lisafamf
ASKER
Hi byundt,
I was able to open the file!
My issues is, I'm a total novice and don't really understand anything your wrote above, lol.
So, are you saying I can get the results I want?
It does work when I put that one particular name in, but there is another name with a 4 in column J, and that is not populating the information?
I am a total novice!!!
Thanks,
Lisa
Lisa,
The posted code and workbook are supposed to solve the problem you posted. And they did so in my tests.
If it doesn't work at your end, please post the workbook that reproduces the problem.
Brad
lisafamf
ASKER
It only worked with that one girls name. Here is the file attached. Thanks!
I trust what you did, it's me I don't trust ;)
Thanks, Brad. mastercalltoEE_Q28960827.xlsm
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
=vlookup3dtest($A19,'Aug ''16'!$A$1:$J$1000,7,FALSE
In the second parameter, note that there are two single quotes preceding 16--not a double quote.
Install the user-defined function in a regular module, just like it was a macro.
As written, the UDF returns #N/A (as text) if it cannot find the test value (4 in your request) in the lookup sheets. Since your test case had one column that was blank and you were returning text, I appended an empty string to the value returned by Vlookup3DTest.
Open in new window