Avatar of lisafamf
lisafamf
Flag for United States of America asked on

Excel Macros? Possibility to do this?

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

Avatar of undefined
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 Variant
Dim ws As Worksheet
Dim wb As Workbook
Dim rg As Range
Dim i As Long, FirstSheetIndex As Long, LastSheetIndex As Long
Dim addr As String
Dim v As Variant
Application.Volatile
Vlookup3DTest = "#N/A"
addr = LookupTable.Address
Set wb = LookupTable.Worksheet.Parent
Set ws = wb.Worksheets(LastSheetName)
FirstSheetIndex = LookupTable.Worksheet.Index
LastSheetIndex = ws.Index
For 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 If
Next
End Function

Open in new window

mastercalltoEE_Q28960827.xlsm
lisafamf

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
byundt

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
byundt

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
ASKER CERTIFIED SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
lisafamf

ASKER
yes, that is what I want, thank you thank you thank you.  I'll open it tomorrow, gotta run.  I sure appreciate  your help :)
Lisa
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Frank Helk

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: byundt (https:#a41741277)

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.

frankhelk
Experts-Exchange Cleanup Volunteer