Forum Widgets
Latest Discussions
Hyperlink doesn't stay in the same row as the data in my Table
I made a worksheet with Dynamic search box using FILTER function and made another worksheet that has the Masterlist, i need to hyperlink Certificate records of the hospital staff the Table but when i put it on the row of someone specific the data becomes a plain text on my search box. i tried also hyperlinking the file on the searchbox as well but the hyperlink stays on the same cell and doesn't follow the row it should be in.ZohmbieMar 25, 2025Occasional Reader7Views0likes1CommentPrinting to .PDF with a check box.
I recently created a template that uses check boxes. However, when I print to .PDF the check box does not show up or print. Is there a setting in the page set up or maybe formatting that will allow these boxes to print to pdf?MicheleMar 25, 2025Occasional Reader3Views0likes1CommentFind Which Emp Number is Associated with Which Person in a Data Set
Good Day! I'm unsure which formula to use, I have a data set that shows First Name, Last Name, Emp Number, and Dates Attended that is auto generated by who shows up in any particular day. (See attached Pic#1) I would like a formula that breaks down which names are associated to each number. So, for example if I type in '6167' into a cell it will show 'Tom Rock' is associated with that number. (See attached Pic #2)GasDetectMar 24, 2025Copper Contributor9Views0likes1CommentRuntime error 1004: Unable to get the Xlookup property of the worksheetfunction class
I have the current MS Office 365, and am using Excel. I created a button on Sheet1 to click [Change Member Details] On click, the fields on the form are initialized, with only those to use for the search being made visible on the "UpdateMemberForm". The user form prompts for required search input (member number, or a phone number, or a combination of name and street address). I entered the member number, and clicked the [Find Member] button on the "UpdateMemberForm". The "WorksheetFunction.XLookup(I_Mem_Num, "A:A", "A:V")" was to search Sheet1, column A, and return the string of data to an array "GetMemberData" that I wanted to subsequently display in all the fields on the user form. The data was to be simply read, or allow for record change/correction and written back to sheet1, or deleted if [Delete Member] button was clicked. The member numbers are in Sheet1 Column A. Each record row is contained within columns A through V. As a test, I used a member number that I knew was in Sheet1 column A. This is the code I am using that returns the error: Runtime error 1004: Unable to get the Xlookup property of the worksheetfunction class, highlighting the XLookup code line. ' >>>>> UPDATE MEMBER <<<<< ' Sub RectangleRoundedCorners3_Click() Set Sheet_Name = ThisWorkbook.Sheets("Sheet1") Dim Array_Count As Long Dim SetSwitch As Boolean Dim GetMemberData As Variant ReDim GetMemberData(0 To 22) As Variant Load UpdateMemberForm UpdateMemberForm.Show End Sub __________________________________________________ ' ' >>>>> Begin User Form Data Input <<<<< ' ========================== Private Sub I_Mem_Num_Enter() I_Mem_Num.BackColor = vbYellow I_Suburb.Visible = False I_Postcode.Visible = False I_Birth.Visible = False I_Age.Visible = False I_Comment.Visible = False I_Email.Visible = False I_Mem_Status.Visible = False I_Mem_Receipt.Visible = False I_Mem_Date_Paid.Visible = False I_Joining_Date.Visible = False I_Film_Fee.Visible = False I_Film_Receipt.Visible = False I_Film_Date_Paid.Visible = False Mem_Category.Visible = False Btn_Gender_Male.Visible = False Btn_Gender_Female.Visible = False Btn_Gender_Other.Visible = False Btn_Vote_Yes.Visible = False Btn_Vote_No.Visible = False Btn_Photo_Yes.Visible = False Btn_Photo_No.Visible = False End Sub Private Sub I_Given_Enter() I_Mem_Num.BackColor = vbWhite I_Given.BackColor = vbYellow End Sub Private Sub I_Surname_Enter() I_Given.BackColor = vbWhite I_Surname.BackColor = vbYellow End Sub Private Sub I_Phone_Enter() I_Surname.BackColor = vbWhite I_Phone.BackColor = vbYellow End Sub Private Sub I_Phone_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Stop_Switch = "Go" Do Until Stop_Switch = "Stop" If IsNumeric(I_Phone.Text) = False Then I_Phone.Text = InputBox("Please enter 8 or 10 digit phone number ") ElseIf IsNumeric(I_Phone.Text) And Len(I_Phone.Text) = 8 Then I_Phone.Text = Format(I_Phone.Text, "0000-0000") Stop_Switch = "Stop" Exit Do ElseIf IsNumeric(I_Phone.Text) And Len(I_Phone.Text) = 10 Then I_Phone.Text = Format(I_Phone.Text, "0000-000-000") Stop_Switch = "Stop" Exit Do Else Stop_Switch = "Go" End If Loop End Sub Private Sub I_Street_Enter() I_Email.BackColor = vbWhite I_Street.BackColor = vbYellow End Sub Private Sub L_Mem_Num_Click() End Sub __________________________________________________ Private Sub Btn_Find_Member_Click() SetSwitch = True ReDim GetMemberData(0 To 21) As Variant Do Until SetSwitch = False If I_Mem_Num.Value > 0 Then MsgBox I_Mem_Num GetMemberData = WorksheetFunction.XLookup(I_Mem_Num, "A:A", "A:V") MsgBox GetMemberData SetSwitch = False Exit Do As a further test, I created Sheet2 using A1 as the record number input to search, with the Xlookup formula in Sheet2, A3. It read the data on sheet1, and returned the array of values from Sheet1 A to V, and worked perfectly. =XLOOKUP(A1,Sheet1!A:A,Sheet1!A:V) Help with a solution to this problem would be greatly appreciated. Thank you in advance.SolvedDebs_auMar 24, 2025Copper Contributor15KViews0likes4Commentsxlookup not finding number/letter mix
I have a column consisting of part numbers: a mix of numbers and digit/letter combos, e.g 2, 3, 3a, 4, 5, 6, 6a, 6b, 6c, 7, 8 ...... I have formatted both the source column and the lookup column as text. But xlookup() only finds the cells which have both digits and letters (i.e. 3a, 6a, 6b, 6c...)quilkinMar 24, 2025Copper Contributor64KViews0likes17CommentsFormula for if a date is more than X days in the past
Can someone help me with a formula for Excel? If Column A returns a set of dates, is there a function to show if the dates in Column A are more than 30, 60, 120 days in the past?SolvedO_edwardsKPPB-9Mar 24, 2025Copper Contributor26KViews0likes7CommentsRemove Date data from date time field
How do you remove date data from a date/time field so that all you have is the time data?SolvedPatSpillaneMar 24, 2025Copper Contributor919Views0likes3CommentsPopulating Schedule based on two Criteria
Hi There - I am working on a master schedule for a camp that will populate two sheets, one that has a full weekly schedule for all groups (this part I've figure out), and the second sheet (or multiple sheets) that will populate the activity schedule. Essentially, I have a master schedule and want to be able to pull two smaller data subsets from it, one by group, and the other by activity. I'm open to any and all changes to make this accessible, thank you! Below is the master schedule with data My group schedule (I can drag and it will fill in columns, so easy there), Any adjustments to master will repopulate Art Schedule, for example, that I want to set up to auto fill in, so any adjustment I make in the master schedule will reflect here. Thanks in advance for any help!RDroMar 24, 2025Occasional Reader5Views0likes0CommentsPower Query by Default Excludes First Blank Column
I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't. When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?heylookitsmeMar 24, 2025Brass Contributor107Views0likes4Comments
Resources
Tags
- excel42,435 Topics
- Formulas and Functions24,608 Topics
- Macros and VBA6,379 Topics
- office 3655,989 Topics
- Excel on Mac2,637 Topics
- BI & Data Analysis2,354 Topics
- Excel for web1,905 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,623 Topics