Calling all excel gurus

Status
Not open for further replies.

Will Gill

Will Gill

Will Gill

Don
Joined
18 Oct 2002
Posts
56,648
Location
Cornwall
'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 said:
Anyone care to steer me in the right direction?
Click to expand...

Windows sub-forum? ;)

I'm afraid that it's probably going to have to be a macro if you want it to update automatically. It's probably a job for access tbh.

Edit: vlookup would work if you had two different lists.

Say you retain your 26 lists, but every new entry you put their name into the one of 26 spreadsheets, and then a master. The master could then use a vlookup to look up data related to each name.

That doesn't sound ideal though.
 
Do each of the sheets have the same formatting for where the email addresses are stored? As in are they in the same column or anything, what is the common thing to use to pickup the location of the addresses so this can be used and just substitute each of the sheets and get the data from them all?

Or do you mean the names of members are on each tab, by letter of their name A-Z?

How many members for that to be useful, rather than as above just put them all one sheet.
 
might be easier with a database, but vlookup sounds the way.
ie with a database you would just give each of them primary and foreign keys and join on that.
For the vlookup to work, you are going to need something that is common to both of the spreadsheets.
It might be easier to maybe load them into access as tables and go from there.
It all depends on what your comfortable with of course
 
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 :)
 
It seems like utter madness to go down the route of VBA macros and risk worksheets not staying up to date and all sorts of random errors to try and mask over the main problem which is bad spreadsheet design/usage!

I see this all the time with work - it seems people's brains break when confronted with the concept of one big list so the automatic response is to break it into multiple small lists, even though that makes it harder to sort, filter, print and just about anything you would actually want to do with a spreadsheet :p
 
Last edited:
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 :p

But I do take your point
 
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)
 
Last edited:
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)
Click to expand...

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 :D
 
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
 
Status
Not open for further replies.
Back
Top Bottom