Drop down menu, with editable options

[fallenmathi]

Help would be greatly appreciated, as i am very new to this, and rather stuck...

what I am trying to create is a small spreadsheet that does the following:

has a dropdown menu with a list of staff on
extra staff can be added to this list

when you select a member of staff- it shows their details.
those details can be edited (so if you select the staff from the drop down menu, and it says age: 16- i am trying to make it so when you type 17, it remembers that/changes that on the other page)

I have attached what i have so far. I managed to create the menus, but I am completely lost, at how to make them editable...

Help would be incredibly appreciated!

Thank you!

lily

[TMS]

I think the only waythat you can do what you want is with VBA. Do you want a VBA solution?

You would need a Change Event monitoring the drop down box and the variables.

Regards, TMS

[fallenmathi]

honestly, i'm just after any solution that works. I know nothing about VBA, so would need a lot of help in setting that up though,

[TMS]

See the attached updated workbook ...

[fallenmathi]

Thank you! you are an absolute hero! - can i ask though, if its not to much - can you add another 15 'tests' so where at the moment, it goes to 5, can it go to 20? - I wish i could do it myself, but im rather lost

also, ive tried to extend the lists, so that i can have more staff (currently it has 19) to do this i tried to change the lists in the Name Manager, so that it would cover everything down to cell 200, but it the spreadsheet then seemed to stop working?

Thank you so much!!

Lily

[fallenmathi]

See the attached updated workbook ...

I just thought id express again, how greatfull i am for your help!
Lily

[TMS]

I will see what I can do

[TMS]

See updated example

[TMS]

Forgot to say, you should be able to add as many staff as you want ... but adding tests might need a bit of adjustment (nothing major). The table (TheTable) is fixed in its width although it is dynamic for its height. So, you'd have to use the Name Manager to widen the table (from V to wherever)

Regards, TMS

[fallenmathi]

Thank you! that is amazing, im always amazed by the generosity of people on the internet...

With regards to adding more tests: ive made the table longer, but i seem to be missing the link between the table and the front page - so when i type things in, it doesnt transfer them across, like all of the other ones do? what i am after is the possiblity of adding 50+ tests, if needed...

How would i go about doing this?


Thank you!

Lily

x

[TMS]

OK, for kisses, I think I can work on this some more.

However, so that you may be in a position to maintain this yourself in the future, I'll try and explain what is going on ... and what needs to change.

In simple terms, you should be able to add variables on Sheet1 with no problem. Similarly, you can add Staff on Sheet2 and their corresponding tests. That's because the named ranges are dynamic, at least in terms of the number of rows. However, the table on Sheet2 is a fixed width and I suspect that's the problem.

So, to explain a little more. The Staff dynamic named range is defined as: =Sheet2!$B$4:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

The way that works is that the first cell in the range is Sheet2!$B$4 ... straightforward. But the second cell comes from indexing column B and the way it does that is to count the non-blank cells in the column ( COUNTA(Sheet2!$B:$B) ) and add in the number of spaces in cells B1 to B11 ... that's just to give a little flexibility if you move the table up or down, that is, add or remove rows. That's how the staff table works, however, it is a single column ... column B.

TheTable dynamic range is defined in exactly the same way but is multiple columns. But the structure of the formula is the same, and for 20 tests, it looks like this:

=Sheet2!$B$4:INDEX(Sheet2!$V:$V,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

To change it to cope with 25 tests, change the two references to column V to column AA, thus:

=Sheet2!$B$4:INDEX(Sheet2!$AA:$AA,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

Following this, if you wanted to extend this to 50 tests, change the V (AA) to AZ, thus:

=Sheet2!$B$4:INDEX(Sheet2!$AZ:$AZ,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

It wouldn't hurt to change it to BZ now to allow for further expansion (76 tests). So long as you don't refer to them on Sheet1, no-one will know.

=Sheet2!$B$4:INDEX(Sheet2!$BZ:$BZ,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))


The attached updated example has 300 staff and 76 tests (although there is no data in the last 26).


I hope this all makes sense and you will be able to adjust it to meet your requirements.

Regards, TMS

[fallenmathi]

OK, for kisses, I think I can work on this some more.

However, so that you may be in a position to maintain this yourself in the future, I'll try and explain what is going on ... and what needs to change.

In simple terms, you should be able to add variables on Sheet1 with no problem. Similarly, you can add Staff on Sheet2 and their corresponding tests. That's because the named ranges are dynamic, at least in terms of the number of rows. However, the table on Sheet2 is a fixed width and I suspect that's the problem.

So, to explain a little more. The Staff dynamic named range is defined as: =Sheet2!$B$4:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

The way that works is that the first cell in the range is Sheet2!$B$4 ... straightforward. But the second cell comes from indexing column B and the way it does that is to count the non-blank cells in the column ( COUNTA(Sheet2!$B:$B) ) and add in the number of spaces in cells B1 to B11 ... that's just to give a little flexibility if you move the table up or down, that is, add or remove rows. That's how the staff table works, however, it is a single column ... column B.

TheTable dynamic range is defined in exactly the same way but is multiple columns. But the structure of the formula is the same, and for 20 tests, it looks like this:

=Sheet2!$B$4:INDEX(Sheet2!$V:$V,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

To change it to cope with 25 tests, change the two references to column V to column AA, thus:

=Sheet2!$B$4:INDEX(Sheet2!$AA:$AA,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

Following this, if you wanted to extend this to 50 tests, change the V (AA) to AZ, thus:

=Sheet2!$B$4:INDEX(Sheet2!$AZ:$AZ,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

It wouldn't hurt to change it to BZ now to allow for further expansion (76 tests). So long as you don't refer to them on Sheet1, no-one will know.

=Sheet2!$B$4:INDEX(Sheet2!$BZ:$BZ,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))


The attached updated example has 300 staff and 76 tests (although there is no data in the last 26).


I hope this all makes sense and you will be able to adjust it to meet your requirements.

Regards, TMS

Thank you so much! - problem is now completly solved! Thank you!

Lily x

[TMS]

You're welcome. I enjoyed working out the solution. Not that difficult in principle but not something I've done before. Good to do something a bit different.

Are you likely to have any issues with people changing or deleting stuff unintentionally?

Regards, TMS