Automate Name entry from a list

Hi,

Let me understand this.

You have 44 names in Q6:Q49.
Are you saying that that you want to fill the 39 cell in the ranges you mentioned using names from the 44 names. The rules being:

1. The 39 names are picked in the strict order of listing in column Q, starting with a name that you choose as the starting name which you put in E26.
2. If the name picked starts at Q12 or after, meaning there are only 38 names beneath, you then rotate to the top of the list at Q6 and start adding names going down until you have picked 39 names
3. The order of filling the columns is first E26:E36 then E46:E57, E62:E63, J26:J36, J38:J40, J46:J49

If that's correct then see the attached workbook. I've made E26 a drop down so you just pick a name.

Hi,

Yes after submitting the workbook it struck me that a picking the first name by random and then having the rest listed randomly would perhaps be useful.

If the current workbook is OK then let me know and I'll put in some random functionality.

See Attached

I've added the following macro.

When you double click in E26 that starts the macro.
It first populates a random list of numbers in AH6:AH49
Then copies all the names from column Q to AG6
Then finally sorts the range AG6:AH49 in ascending order of column AH

The names in the body of the report are now linked with a simple =AG6, =AG7, =AG8 ...etc to the randomised list of names.

Please Login or Register  to view this content.

Richard,

Both solutions are great. Thank you.

I was look for a MACRO for your first solution then I realized you created a formula to solve it. In my mind I was thinking it you require some sort of LOOP and IF statements.

I'm a few hours ahead of you here in Japan and off to work now. As I'm in the US Navy as my have guessed.

Again, Thank you.

V/r,
Jim

My pleasure and thanks for the rep.

Yes it was sort of clear that you're in the US Navy

Presumably you're at the Naval base at Tokyo Harbour?
I've spent quite a lot of time in Japan, much of it in Tokyo so I know it fairly well.
As you clearly know it's a wonderful culture and so different to the West, although it has been gradually changing over the last 30 years.

I first went to Tokyo in 1991 and then many times over the next 30+ years. I was last there about three years ago and found it had changed quite a lot.

Hi,

Could you clarify what you mean by a Spot Checker name change causing a formula being lost please.

What do you do to change a Spot Checker name, i.e. which cells are altered (manually or by running a macro and if so which macro) and which formula gets lost?

I've tried changing a name in column Q in the Report tab and from G20 onwards in the Admin tab then running the Sub KhakiSptCkr_RptTable() macro, but this doesn't change any of the formulae I used so I'm puzzled at the moment.

Which version of the two solutions I sent are you thinking of. The very first, which I'll call the 'rotating names' where the names stay in the same cyclical order but in different cells on the Report, OR the second version which randomises names?

OK. Understood.

I had assumed that column Q on Report was where you kept the master list of names.
To obtain the functionality you want will require a formulae in those cell ranges you mention.

It sounds like the essential requirement is one of controlling the names available. i.e. being able to edit, add or delete Names from the system. It sounds like where those names are listed is not relevant to the user.

The functionality you want would be better achieved I suggest by giving the user a pop up form which would allow them to manage the names. The names would continue to be listed in column Q although the user would not need to know about or use this range at all. When a name needed adding or deleting then there could be a button on the Report form that they click and then they'd see a pop up form for managing the names.

I notice the master list of names seems to be G20:G80 on the Admin Report since this currently contains 50 names. Are all these available to be used for column Q on the Report (and then via formula in the specific ranges you have identified)?
If so it seems that the pop up Names management form should work with those names rather than those the Report column Q. The Pop up form would contain all the 8 pieces of information that the table in E20:L80 requires

Does that seem a way forward?