Way to manage events, attendees and trends?

[candymycandy]

Hi all,

Just looking for some ideas. I need to create an interactive file whereby users can manage company events. The data includes things like attendees, dates, conference names etc. Ideally we want to be able to offer insights such as how many conferences as X attendee been to, did he go to the last one or not (e.g. so we can email him to invite him etc). How much has been spent etc. I know this can be done in excel but I need it in a clean way. The current file has lots of tabs, massive sheets etc. Would be good to have in a dashboard format etc and ultimately be easy for the user to update and use. Wondering if anyone else has created such a thing and could give me some pointers? Many thanks.

 

[etaf]

maybe 1 sheet with the data

name of event / date of event - perhaps nore than one /location / name of attendee / date attending /

how do you record spend , and did he/she attend
is this an extraction from an existing data base - or does someone sit and enter all the data into a spreadsheet

probbaly best to think of all the summary - dashboard data you would like to present , which would help inform you of the data you need to have

sounds like a crm system ......

 

[candymycandy]

Hi etaf, thanks for your reply.

A messy version of the data exists already but the users do enter the data in themselves (attendees, ticket cost etc).

I think some of the main requirements (and the bits I am stuck on) are:

1. Displaying attendees and which events they've been to. I am not even sure it is possible in excel.
2. Where a previous company is not using the allocated allowance for their company to come to these events/how much a company has spent
3. both in an easily readable format that is usable and updates automatically

A CRM system is a good description I think.

 

[etaf]

ok, lots of CRM software out there
BUT it maybe possible in excel - just depends on the data entry and if in a format that will allow a dashboard with the criteria you need

a lits of attendies and events should be OK - maybe with a FILTER() and sort functions
or even a pivot table

so Events and clients

Book3
	A	B	C	D	E	F	G	H
1	client	event		client	sorted by event		sorted by client	event
2	1 -name	1-Event		1 -name	1-Event		1 -name	1-Event
3	2 -name	1-Event		2 -name	1-Event		1 -name	2-Event
4	3 -name	1-Event		3 -name	1-Event		13 -name	3-Event
5	4 -name	1-Event		4 -name	1-Event		14 -name	3-Event
6	5 -name	1-Event		5 -name	1-Event		15 -name	3-Event
7	6 -name	2-Event		6 -name	2-Event		2 -name	1-Event
8	3 -name	2-Event		3 -name	2-Event		2 -name	3-Event
9	4 -name	2-Event		4 -name	2-Event		3 -name	1-Event
10	5 -name	2-Event		5 -name	2-Event		3 -name	2-Event
11	1 -name	2-Event		1 -name	2-Event		4 -name	1-Event
12	2 -name	3-Event		2 -name	3-Event		4 -name	2-Event
13	6 -name	3-Event		6 -name	3-Event		5 -name	1-Event
14	13 -name	3-Event		13 -name	3-Event		5 -name	2-Event
15	14 -name	3-Event		14 -name	3-Event		6 -name	2-Event
16	15 -name	3-Event		15 -name	3-Event		6 -name	3-Event
Sheet1

Cell Formulas
Range	Formula
D2:E16	D2	=SORT(FILTER(A2:B16,B2:B16<>""),2)
G2:H16	G2	=SORT(FILTER(A2:B16,B2:B16<>""),1)
Dynamic array formulas.

 

[etaf]

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[candymycandy]

Hi thanks for getting back to me again. Would love to use some 3rd party software but not able to (£££!). I didn't have permissions for the add in but please see google share, hopefully it works ok and is clear for you and many thanks for taking the time to look!

anondata.xlsx

(there is a formula on this that doesn't work in google sheets)

 

[etaf]

i used the following MID function - which worked in google sheets
=mid(B3,find("@",B3,1)+1, (find(".",B3,find("@",B3,1))-1) -(find("@",B3,1)))
and then i put the name into F - just to test it works
then used a SUMIF()
=sumif($F$3:$F$22,G3,$D$3:$D$22)

But if you put the MID function into column C then the sum would change to
=sumif($C$3:$C$22,G3,$D$3:$D$22)

the mid function you used works in excel OK

google sheets

mid-sum

docs.google.com

anyway

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1	events (user entry)	attendee (user entry)	formula to extract company name	cost of ticket per person (user entry, though would be good if could pull from another sheet where all the event data is filled in maybe?)		Breakdown of what companies have spent on events. With their membership of our company, they get £x allowance to use on events each year, so want to see what they have used, and what remains. E.G.:	Need formula to extract unique company names?	This can be entered by user	Need to know what has been used. Each event cost something different which is where I'm stuck!	Need to know what is remaining of their £ allocation
2	Event	DELEGATES	Company	Ticket cost			Company	£ Allocation	Used	Remaining
3	The Big Conference - 11/11/2023 - 3 pm	Mrs.bloggs@gmail.com	gmail	100		gmail	Amazon	5000	200	4700
4	The Big Conference - 11/11/2023 - 3 pm	Mrbloggs@gmail.com	gmail	100		gmail	Google	10000	300	1000
5	The Big Conference - 11/11/2023 - 3 pm	sara@asda.co.uk	asda	100		asda	Myco	1000	200	0
6	The Big Conference - 11/11/2023 - 3 pm	harry@google.com	google	100		google	Bigco	3000	200	1000
7	The Big Conference - 11/11/2023 - 3 pm	Mary@company.com	company	100		company	Gmail		750
8	The Big Conference - 11/11/2023 - 3 pm	Susan@magic.com	magic	100		magic			0
9	Our training event - 01/10/2023 - 9am	Test@amazon.com	amazon	200		amazon			0
10	Our training event - 01/10/2023 - 9am	email@yahoo.com	yahoo	200		yahoo
11	Our training event - 01/10/2023 - 9am	hello@company.com	company	200		company
Data

Cell Formulas
Range	Formula
I3:I9	I3	=SUMIF($F$3:$F$22,G3,$D$3:$D$22)
C3,C5:C11	C3	=MID(LEFT(B3,FIND(".",B3,FIND("@",B3))-1),FIND("@",B3)+1,99)
C4	C4	=MID(LEFT(B19,FIND(".",B19,FIND("@",B19))-1),FIND("@",B19)+1,99)
F3:F11	F3	=MID(B3,FIND("@",B3,1)+1, (FIND(".",B3,FIND("@",B3,1))-1) -(FIND("@",B3,1)))

mid-sum

docs.google.com

 

[candymycandy]

Sorry for the delay, just had time to test this out now and it works!

Thanks so much

 

[etaf]

you are welcome