Solved

Drop down list to alter other cells

Posted on 2014-10-12
26
180 Views
Last Modified: 2014-10-25
I want to use a drop-down list to decide what information appears in cells elsewhere. I have attached the sheet in question with this enquiry.
If I choose 'Slab-mount' at C2, I want the information in B11:D13 to disappear entirely. If I choose 'Tank-mount' at C2, I want the information in B6:D9 to disappear entirely.
Furthermore, if I choose 'Slab-mount' at C2, then I also want the information in cells B4:C4 to disappear entirely.
Also, if I choose 'Semi-spherical' in C4, then I need the data at C12 and C13 to point to a particular work sheet (not shown in my included example), whereas if I choose 'Ellipsoidal' in C4 then I need the data at C12 and C13 to point to another worksheet. I know how to handle these worksheets with the input data from C12 and C13, once I know how to direct this data to the right place.
Finally, if I have selected 'Slab-mount' at C2, then the data at C7, C8 and C9 can be directed to yet another worksheet, which I know how to handle.
So the summary is: how do I use drop-down boxes, and the results thereof, to determine what information appears in other cells to enable a user to only see and select the information relevant to the case in hand? If he choses a 'Slab-mount', he doesn't need to see anything about a 'Tank-mount', and vice-versa. If he does chose the 'Tank-mount', he can then answer the further question about its geometry, which won't change the numbers he enters at C12 and C13, but will enable me to direct those numbers to a different worksheet (the calculations for a semi-spherical design being different from an ellipsoidal design). I can deal with all the 'downstream' issues, but do not know how to enable this 'upstream' selection of fundamental principles.
Trial-price-sheet-for-Exp-Exch.---141012
0
Comment
Question by:BlosMusic
  • 11
  • 10
  • 4
26 Comments
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
Just passing and on mobile and your link doesn't to open here, but few pointers to start with.. for drop down your easiest way is to use data validation with a list imo,

e.g. for 2007 onwards Data tab, data validation, data validation, choose list.  In source you can use a list you put in there but easier if you used a named range.  Would suggest a "configuration" sheet or similar.  In there put your list of choices, then highlight the list and type a name for it in the box above A1 and press Return - you can also do that in Name Manager (Formulas tab).  Then in the list data validation you just put =YourRangeName

You can define a pop up message and validation message in the data validation area too.

For making the data disappear are you talking blank out the cells, i.e. lose the data already there, leave the data there but stop the user being to edit it etc?  Also are those cells formulas doing lookups or for users to enter data?

Steve
0
 

Author Comment

by:BlosMusic
Comment Utility
Hi Steve,
I can do the Data Validation list operation without a problem; my question related more to the question of "how do I use the results of the drop down to make another cell or cells disappear?". The best thing would be if you can look at my spreadsheet - my question may become clearer then.
Thanks so far, though, for your interest. No one else has come back to me yet! Maybe I'm asking the impossible - or maybe it's a VBA thing.
Richard
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
I would suggest it is VBA though do you mean delete the cells data, hide the cell, etc...

You can right click tab of sheet, view code and use a worksheet_change event such as this and can therefore control any aspect of the cells, lock them etc.

or you can do it with conditional formatting to a certain extent by making colors the same as background etc, .e.g

highlight b5:c5, add new rule, based on formula.
if ($c2<>"Tank-mount",True,False)
Then set the font colour and background color to grey, or white say.

Haven't got time to do any more at the mo. as paid work calls! Sure some other excel people will be along or will look back when I can.
Trial-price-sheet-for-Exp-Exch.xlsm
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
Hi

To give you the best answer I suppose it is worth revisiting your criteria for the end result as there are (as dragon-it hinted) umpteen ways of achieving what you need.

The first question would be whether you need to hide info or display info?

This will determine if you should use a vlookup or index/match formula to populate the fields below. You could for instance run two separate functions from the SLAB or TANK mounted dropdown. One would cause the TANK shape fields to hide, the other would display the relevant info that you lookup.

For this we would need to know also the content of the other worksheets or at least the rationale for having mutiple other worksheets.

So hence the second question, is the data on the other worksheets user entry data or just value lookup data? Or in other words do you want the user to know of all the other worksheets?

If the user is oblivious to other worksheets and it is only to keep things orderly I suggest to use one data worksheet only and name your tables with proper names rather than cell references, and use the names in your formulas to keep things tidy.

Assuming answer to Q1 is you need to display only relevant data and Q2 is that the additional worksheets (WS) are for lookup only.
I would start with creating a dropdown via Data Validation. Enter the Data Values in a list on your Data lookup WS, or use your "SM or TT" table.  Then name that list "Selector"  by selecting the list (i.e. the 2 Cells) and typing the name in the Name Box left of the formula bar. Now go to your input cell on the user WS (C2) and go to Data Validation, select List and type "=Selector" in the Source field, without the quotes (").

Now you can build your Table for the two main Gasholder Types in the Data WS. Decide on the field values that you need. Clearly there are multiple values for TANK shaped holders so you need 2 columns for that.

The idea is that you make a table that looks up the values that you need to display on your user worksheet in the area B6:D9, rather than hiding existing data.

You have field 1,2,3... with descriptions for values and then the corresponding values. So if SLAB is selected it displays:
Volume in field 1
Aspect ratio in field 2
Membrane gap in field 3

likewise if TANK is selected it displays the values depending on which shape (Ellipsoidal or Semi-spherical) is selected.

At the same time it will retrieve the data values for the displayed description by having a similar lookup formula in the value fields.

The only other field you need to worry about then is the dropdown for the TANK shape. The Selector will define whether this is displayed at all  based on dragon-it's suggestion of conditional formatting, using Border, Font Color and Fill attributes for either TANK (full format) or SLAB (blend in with background) when C2 changes.

If it is displayed and value changes it will display varying description and values based on the selected shape.

In Summary

You need to decide what is the easiest route to success and best use minimum worksheets and less complicated formulas, using named references in your sheet will make formulas easier as you can refer to the name rather than 'worksheetname'!(Cellrange). And displaying rather than hiding will make it easier to build a dynamic user sheet based on your selection.

I appreciate that this is quite broad advise, but a) I don't know how well you know Excel, so don't want to give too much detail straight away and b) I have no idea whether my assumption re user and lookup data are correct, although I think you ought to consider this principle as you r problem is similar to what I encounter in my work.

Hth
capt.
0
 

Author Comment

by:BlosMusic
Comment Utility
This looks excellent and thorough. I shall read it thoroughly, and digest it, and then play with it.
In the meantime - thanks!! Brilliant.
Richard
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
Hi Richard

no problem. However please note that it only provides a birds eye view of what is needed and you may want/need more specific instructions.

As and when you have digested above please post back.
cheers
capt.
0
 

Author Comment

by:BlosMusic
Comment Utility
Will do capt. Thanks very much.
0
 

Author Comment

by:BlosMusic
Comment Utility
Capt.
I've gone through your fine exposition, and offer the following further information:-
1. All the other worksheets (there are quite a few, and I didn't show them on the example I put on Experts Exchange, partly for reasons of confidentiality, and partly because I don't think it's necessary) - all these other worksheets are simply value lookup data, and the user doesn't need to see these. I might not have done it very efficiently, but it's good enough.
2. You are quite right - I don't want the user to be aware of the other worksheets.
3. The numbers that the user can put into C7:C9 and C12:C13 can be anything - I don't want to have a pre-selected list of values, which I think (only think!) is what you are suggesting.
4. Once the values have been put into these cells (either C7:C9 or C12:C13, but not both), then I know how to make that information work throughout the rest of the worksheets. All I want to do is give the user the option of either putting in the values of "volume", "aspect ratio" and "membrane gap" in C7:C9 if it is a slab-mounted gasholder, OR "base diameter" and "height" if it's a tank-mounted gasholder; and I want the programming to not show cells C7:C9 if the user chooses "Tank-mounted", and not show cells C12:C13 if he chooses "Slab-mounted".
5. If the user chooses "Tank-mounted" then I want him to be offered the further choice of "ellipsoidal" or "semi-spherical", whilst of course not showing anything about "slab-mounted" data (Cells C7:C9 being suppressed if he has chosen "Tank-mounted"). For either of these choices (ellipsoidal/semi-spherical), the entry by the user of the base diameter and height will give me the numbers I can then use in all my other (hidden) worksheets. And if he chooses "slab-mounted", well, the cells for "tank-mount" will all disappear. (At least, that's what I want!)
SUMMARY
Currently I have the system working such that the Excel Spreadsheet will calculate all the geometry for a slab-mounted gasholder. It works very well. The user puts in the volume, aspect ratio and membrane gap, and hey presto! I get my answer (which is, ultimately, a price of the gasholder). I have in the past done something similar for the Tank-mounted version. I know how to use the base diameter and height of the Tank-mounted version to generate my price, and my worksheets will do this for me - but . . . . I just need this initial input information to force the user to decide whether or not he has a slab-mounted thing, or a tank-mounted thing (and if tank-mounted, the geometry he wants), and then be presented with just the fields he needs, and into which he can put more or less anything (BTW I know how to limit the number he enters between certain values). If he has a slab-mounted gasholder, he will invariably know the volume he wants, and the aspect ratio; and if he want a tank-mounted gasholder, he will always have a given tank diameter that the gasholder has to fit on, and usually he doesn't care about the volume. So the inputs of "volume", "aspect ratio" and "membrane gap" are entirely relevant to the slab-mounts, and "base diameter" and "height" are entirely relevant to the tank-mounts. Both sets of figures ultimately lead to the same geometry, but the user will really only have the information to input as I've described above.
Am I asking for something that can't be done?
To answer the other question (in your Summary), I am sort of OK on Excel for formulae and so on, but some of these darker arts are beyond my scope.
Ultimately, I want a simple sheet for the user that asks him to input certain data. Most of this data is simple, but it's the bit about making him decide the type of gasholder he wants, and allowing me to then use this simple data that he has chosen (which I can do), that foxes me.
Too many words, perhaps . . . . . but does that make anything clearer?
Thanks for your interest. Hope I haven't spoiled your evening!!
Best wishes,
Richard
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
A lot of words haha :) but I see what you are getting at.

Only one question: Do you directly reference any user input in another formula? I.e. if the user inputs a value does this do a straight calculation from this value in a hidden sheet? This is just so I know if we need multiple value fields or if we can use the same fields for all the configurations.

I am heading out for dinner in an hour so may not have a chance to look at any of this until I am back later on tonight/tomorrow evening.

cheers
capt.
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
One more thing, are there more than the 2 options "Base diameter" and "Height" for ellipsoidal TANK shapes?
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
OK just as a concept and as I am not around tomorrow daytime to check your response to above, please see attached a modified Excel with a new First Input worksheet. I have left yours in and renamed it.

The new worksheet should do what you want. I have also adjusted the Locked Format for the user cells (dropdowns and standard) so you only have to lock the sheet for review (ideally with pw) to keep any users from killing the formulas. if you want to hide the formulas set the format to 'Hidden'.

I have adjusted the colour scheme so you see functionality easier.

It contains a few =if functions validating against the Selector and TankShape tables as well as conditional formatting with coloured background changes depending on B4 being visible or not.

All difficult to explain, hope it makes more sense when you check it. And please do as I usually leave a bug in by accident...

But in theory it should just change depending on the selection of the mount type....

The only thing is that you may have to deal with residue data if people change mount types, but I thought adding a macro to zero out user cells would have been a step too far. ;)

cheers
capt.
TrialPrice.xlsx
0
 

Author Comment

by:BlosMusic
Comment Utility
Dear capt.
Well, that does the trick, perfectly. I think I can see what you've done, and I think I'm learning.
There are no other necessary inputs for the ellipsoidal tank shape - why do you ask? Other than that - I would like to change the cell colour scheme, and I will be adding quite a lot of other data to your "First Input" page, all of which i think i can handle OK.
So this does the job almost entirely as I wanted it. Brilliant.
One thing - I didn't really understand your "the only thing is that you may have to deal with residue data if people change mount types" comment.
Richard
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:captain
Comment Utility
Hi Richard, thanks for the comments, obviously the colour scheme is a bit bland right now, just make sure that you change the conditional formatting to fit whatever colour you choose.

To answer your questions.
There are no other necessary inputs for the ellipsoidal tank shape - why do you ask?
This was to ascertain if the user gets prompted with different descriptions if the tank shape changes or if they are the same and only differs by mounting type. Otherwise the formulas would ave had to cater for a 4 way split (3 types plus blank)

I didn't really understand your "the only thing is that you may have to deal with residue data if people change mount types" comment.  This is just so that you keep in mind that users are users, so they may not clean up the data they input. I was just assuming that there may be an issue if a user has both mount types, and if they were to complete the Excel with SLAB mount first and then leave the value in the 3rd line after they changed to TANK mount. Hence my comment on clearing fields. But it may not be an issue depending on your user profile and the way you disseminate your form.

Anyway glad you find this useful, but feel free to ask if you need further info.

cheers
capt.
0
 

Author Comment

by:BlosMusic
Comment Utility
Hi capt.
I think we're nearly there! All OK - except (and excuse my relative Excel ignorance), I have altered the colour scheme, but I can't seem to get rid of some of the grey cells and I also can't seem to get back some of my borders. I don't necessarily want the colours and borders as I have shown them on the attachment, but I am a bit foxed generally about this. And what is 'conditional formatting'? I don't find the Format tab with it's Format cells/Fill option very intuitive.
Colours-and-borders.xlsx
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
The conditional formatting is what is doing the grey colour, that was doing the hiding, same as I used in my example sheet originally - have been busy on other things and could see you were getting help you needed anyway.

Home tab, conditional formatting button, manage rules is what you need with the relevant cell(s) selected.

Any of the cells that "disappear" or change formatting when you change on of the values are doing this.

As captain has said this does leave all the values the same just hidden so your formulas need to work on those - I adjusted the conditional formatting for your third set of data which turned grey but the text didn't so an existing typed in value would still show.

Would also suggest you use "lock" and "unlock" on all the fields - last tab of formatting for those fields then use the Protection option on the review tab and the user can then only click onto and type into the fields you want.

What I would then is like attached, see Variations sheet, the "values selected" area then fills in fields on there based on the values on the front page - I have named them "Field1, Field2, Field3" on the front page and then on this sheet it shows them in the right cell.  They have names which you can see next to it so you can use those in your calculations, e.g.

=Slab_Volume*Slab_MembraneGap or whatever!

Steve
TrialPrice.xlsx
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
HI Richard

Steve is correct, happy to help further but please let me know if you prefer me working from Steve's xlsx or yours so we don't cross wires.

Thanks
capt.
0
 
LVL 43

Expert Comment

by:Steve Knight
Comment Utility
Was only minor change to your last posted sheet, captain.  The other one posted only had a partial front sheet there by the looks of it.  Anyway back off into hiding now, things to do!

Steve
0
 

Author Comment

by:BlosMusic
Comment Utility
Steve and Captain,
Most impressed by this! You guys are so helpful! I am working today, and hope to get back to this tonight.
You must understand that whilst I can work with Excel on ordinary things, this stuff that you are helping me with is all new!
But thanks both for all your help. it is greatly appreciated.
Richard
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
Hi Richard

no problem at all.

So what I gather from your post is that you want the functionality as in my Excel form but with your colour scheme as in your Colours and Borders xls?

Steve has added some field names so it makes sense to work from that and change the colour scheme accordingly. It would make sense you do it yourself to benefit from this for future projects but I am happy to change the conditional formatting for you. The criteria is difficult to explain as it formats depending on cell values, sometimes on the basis of other cells than the currently selected so I can see how that can get confusing.

I am on dayjob today too, maybe you can post quickly where you are at and I have a look tonight.

cheers
capt.
0
 

Author Comment

by:BlosMusic
Comment Utility
Captain,
Exactly right.
I attach the 'First Input' sheet to show what I am trying to lead onto. This works well for me, in formatting terms anyway. I want, eventually, to have just this sheet to be filled in - but only where the yellow boxes with red numbers are - and all the subsequent worksheets (which I don't really want to send over the internet, but am happy for you to see privately) will do all the work behind the scenes, and send the answers back to this front page (the First Input page, which I shall rename.). So by inputting various bits and pieces on the First Input page, the result will be a price at the bottom of the First Input page, plus various other salient bits of data that are needed.
The work book is for us here at my company to get accurate and quick pricing for any structure, and to go on from there to generate a full quotation (which i have done in the past, reasonably successfully) - and I am happy that it will do this - but for one of our US clients, I want him to just have the front sheet for input, and the answers will appear on that front sheet as well, so that he can immediately get our price (held for a year or so)  to add to his quotations for complete jobs. But I don't want him seeing our profit margins and so on, for obvious reasons. If I could send you the whole caboodle privately, you could see what I am trying to do - but if you do see it, you will probably laugh at the huge inefficiency of my effort - which has grown in a gangly fashion like Topsy.
Anyway, just being able to give the option of 'Tank-mount' and 'Slab-mount' has been a big step forward.
And yes, I can see that "the criteria are difficult to explain as it formats depending on cell values, sometimes on the basis of other cells than the currently selected so I can see how that can get confusing". I do find this bit very confusing!!

BTW - the drop-down boxes that I have added on 'First Input' obviously won't work, as I've only sent the one worksheet - but basically they simply pick up various further choices of materials, sizes, strengths and so on. My other worksheets can deal with all that OK (if inefficiently!).
Cheers,
Richard
First-input-format.xlsx
0
 
LVL 30

Accepted Solution

by:
captain earned 500 total points
Comment Utility
Hi Richard

no worries, I know exactly what you are coming from. I am a great believer to get code or formulas to work first, you can then always deal with inefficiencies later. Excel is very powerful and there are many functions that I don't even know, so some people may laugh at my version, haha.

As to the conditional formatting, it is easy when you drafted it but a nightmare to explain in words. My sentence was probably too long winded but it was trying to convey that for example cell A1 may change its colour not based on its own value but on the value in B1 so it is difficult to explain conditions upon which cells change behaviour when you cannot show it but only write down the steps.

I have just opened your XLS  and noticed that you have links in there referring to the TrialPrice in your temp folder. I assume this is based on you simply copying out the Input sheet.

I also noted the grey is back...Are you happy to keep the grey colour scheme? I am a  bit confused myself now what the next step would be, and what I can help you with.

If you are happy with everything and want to keep the grey scheme then this may just suffice, but if you want a different colour scheme let me know and I will amend it.

Your ultimate goal btw, is easy to achieve. Once all your formulas work and the worksheets in your file are all complete, hide the worksheets you don't want anyone to see (rightclick on the tab>hide) and then protect the workbook from the Review Menu. Please note though that this is not hack proof and there are possibilities for your clients to get access to the workbook so you may want to think about hiding your margin or not include any costs but charge out prices with the file only.

If you want me to look at anything confidentially, message me on here and I give you my private email address.

cheers
capt.
0
 

Author Comment

by:BlosMusic
Comment Utility
Captain -
Well I don't want to trouble you too much with my problems, but if I tidy up what I have done, I would quite like you to take a look. Maybe I'm a bit paranoid, but there are people out there who may well see some of my background data. Let me play around with it for a day or so, and then - may I send it to you privately for your comments? But please don't laugh out loud! Some of it is redundant, some of it unnecessary, and some of it downright wrong. I am a complete amateur. So . . . . if you would, then please let me have your email address, and you can spend a pleasant half hour laughing.
But I need tomorrow to make a few changes that I need to do in order for you to see the general drift.
Cheers,
Richard
0
 
LVL 30

Expert Comment

by:captain
Comment Utility
I have messaged you :o)
0
 

Author Comment

by:BlosMusic
Comment Utility
Capt.
I have responded! BTW - I haven't had time to work on it today, so you're getting very much unfinished stuff!
Richard
0
 

Author Closing Comment

by:BlosMusic
Comment Utility
Exceptional and expert help from Captain. Very impressive and so helpful. Brilliant.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now