Macro formula not working

Hi Gred, 

I dont think there is a need of macro for this. Please mention which version of excel you are using. 

1.In older verion Normal Pivot table is a better option , if you Double click on any name u will get filtered list related to that name in the next sheet.

2. If u use office 365 you can use xlookup function to retrieve full Raw data from the first sheet
XLOOKUP(lookupvalue,lookup_colum,result range(u can chose range A:BL),"No such person(if not found)")

First u can make a list of names using  Data Validation 

and Please type xlookup formula Where u want detail

Or u can also use filter function

Thanks 

Gred

Suggest you try VLookup (which works in all versions of Excel) but first you need to remove the flawed VBA code which causes your file to fail.

Put this in cell C4 of the "data" sheet

=VLOOKUP($C$2, names!$A$6:$BL$45, 2, FALSE)

where the $ signs are important since they fix row and/or column of the ranges.and the FALSE makes it look for an exact match.

The 2 in bold means if the value C2 is found in the first colum of names!$A$6:$BL$45, show the value from column 2 of that row.

To get the date of birth in cell C8, paste the same formula but change the bold 2 to a 6 (since that data is in the column 6, F), so

=VLOOKUP($C$2, names!$A$6:$BL$45, 6, FALSE)

Change C2 to Wicha and their date of birth will appear (if you have cell formatted to display date). You get the idea hopefully- one formula with just one number to change.

Check in the Tutorials section to see how data validation can help (so you just pick names from a drop-down list in C2)

Revision 25 July 2023:

In the attached revised file, the flawed macros (and hyperlinks) have been removed and the file saved as a normal (non-macro) Excel file.

Cell C2 (yellow) in worksheet data now has List data validation, using this formula:

=names!$A$6:$A$45

Click in it and you can pick one of the 45 names on the names sheet. That will change the green cells C4 and C8 (which have the formula mentioned above). You need to change the formula so all data cells link correctly to the names sheet column.

The slight problem with that is the namesa re as they appear in the table. If you have a later version of Excel (2019 or 365 say), you could have a hidden sheet and put this array formula in single cell in an empty column:

=SORT(PROPER(names!$A$6:$A$45))

Hope this helps. If so, please be sure to mark this Answer as Selected.