Calling all excel gurus

Will Gill · 2 Dec 2014 at 18:17

'evening

I volunteered for the task to revamp my local community club's membership spreadsheet, easy enough, created 26 tabs/worksheets for a to z and populated them with the relevant details. however, what I'd like to now do (and obviously can't) is for every worksheet to automatically update a 'data' worksheet which simply contains a list of every email address from each worksheet - for mailing list purposes - however I thought I knew what I was doing, clearly I don't.

Anyone care to steer me in the right direction? autofill, concatenate etc..?

whats the correct formula, or do I need to write a macro :eek:

Thanks in advance (and no I won't settle for just pooing in each person's mailbox)

Will Gill · 2 Dec 2014 at 18:30

weringo said:
Why have 26 tabs? Just have one tab and use the filter function should you need filter by name.

because the 86 year old ladies understand tabs, filter is witchcraft

Will Gill · 2 Dec 2014 at 18:45

dirtychinchilla said:
Is this your way of meeting women?

It's worked faultlessly so far

Pez said:
I have a better solution (in Excel) I think, send me a copy of the data to my trust address and I'll sort it

Cheers you sexy beast, email inc

Will Gill · 2 Dec 2014 at 20:20

If it was my spreadsheet it wouldnt be a problem, its all the blue rinse brigade & WI crew, they understand tabs because all their address books had them, they didn't however have text filter buttons

But I do take your point

Will Gill · 2 Dec 2014 at 21:25

estebanrey said:
Most proficient way would be to use a macro that re-builds the list on the 'Data' tab everytime the spreadsheet is opened.

If you're desperate to avoid using macros you could do it by formulas but only if you reverse the process; that being new entries are input into the data tab and the a-z tabs populate from that.

Here is an example of the latter (only did A-C for an example but the principle is the same)...

https://www.dropbox.com/s/hdq1lqkunxyircu/example.xlsx?dl=1

*The added advantage of doing is this way if you only need to sort the data tab to sort all the a-z tabs at the same time (presuming the old biddies will want to retain alphabetical order when adding)

Wow thats pretty much perfect, its kind of reversed the request but gives exactly the same result.

Could I be cheeky, the fields I require are actually A-Z

First Name
Surname
Whole Name (first two combined if poss)
Member Status
Address
Email
Renewal

if that's easy for you to do that would be amazing otherwise I can go through the formula and try and work it out myself - which might well do me some good

Will Gill · 2 Dec 2014 at 21:28

estebanrey said:
Give me 30 Will, I'll do a full one

May all your traffic lights be green squire :cool:

Will Gill · 2 Dec 2014 at 23:19

estebanrey said:
Here you go.

Bear in mind, there are A LOT of vlookups in this so there is a recalculation delay when entering data into each cell. You can type past this (so only recalculate after the last column entry

You can also remove a lot of the rows if you want. Currently it can take 5,000 entries in total and 500 for each letter. Just delete the rows on each tab to reduce the number of formulas as I'm guessing they don't have that many members.

Or you can turn off automatic calculation and teach grandma how to press F9 to do it manually.

P.S Remember not to do a whole row delete to remove data in the data tab as this will delete the formulas in the hidden columns!

https://www.dropbox.com/s/hdq1lqkunxyircu/example.xlsx?dl=1

You have made an old man very happy matey, thank you very much

xxx

Will Gill · 3 Dec 2014 at 08:40

The vlookup spreadsheet works a charm, I actually understand whats going with that one, I'll have a play around with the two routines kindly mentioned above but for now I'm one happy camper