Good morning, hope everyone is getting a nice three day weekend and has a safe and excellent holiday
I've looked in several places for an answer and searched my books and can't seem to find an answer,
I'm sure it's something I'm overlooking or just not using the right terms for searching. It seems like
the answer should be pretty smiple but I'm having no luck.
This formula works well in the sheet but I want to use a form to give the user the option to search by
member names in a list box. The form has a list box with members, textbox1 that displays the member name
after the name in the listbox is clicked on Textbox2 that shows the sum of the data and texbox3 that shows
the average of the data
ActiveCell.FormulaR1C1 = _
"=AVERAGEIFS(R[-1]C[-2]:R[24997]C[-2],R[-1]C[-13]:R[24997]C[-13],""John Q Public"")"
ActiveCell.FormulaR1C1 = _
"=SUMIFS(RC[-2]:R[24998]C[-2],RC[-13]:R[24998]C[-13],""Jane Doe"")"
Those two formulas work perfect on the spreadsheet but how do I tell the form
that once listbox1 has changed (clicked on any member of the listbox - currently about 300)
display the Sum in TexBox 2 and the average in TextBox3. Names are in A2 to 25000 and numbers to average or sum are in
L2.L25000
Any help would be greatly appreciated. Thanks
I've looked in several places for an answer and searched my books and can't seem to find an answer,
I'm sure it's something I'm overlooking or just not using the right terms for searching. It seems like
the answer should be pretty smiple but I'm having no luck.
This formula works well in the sheet but I want to use a form to give the user the option to search by
member names in a list box. The form has a list box with members, textbox1 that displays the member name
after the name in the listbox is clicked on Textbox2 that shows the sum of the data and texbox3 that shows
the average of the data
ActiveCell.FormulaR1C1 = _
"=AVERAGEIFS(R[-1]C[-2]:R[24997]C[-2],R[-1]C[-13]:R[24997]C[-13],""John Q Public"")"
ActiveCell.FormulaR1C1 = _
"=SUMIFS(RC[-2]:R[24998]C[-2],RC[-13]:R[24998]C[-13],""Jane Doe"")"
Those two formulas work perfect on the spreadsheet but how do I tell the form
that once listbox1 has changed (clicked on any member of the listbox - currently about 300)
display the Sum in TexBox 2 and the average in TextBox3. Names are in A2 to 25000 and numbers to average or sum are in
L2.L25000
Any help would be greatly appreciated. Thanks
