Named Ranges and data validation

[henryjohn]

Hi All,

I'm a new member as of today so be gentle with me ....I'll try and explain what I'm trying to do

I have within name manager create a item called widget which has a value of 50...(widget=50)

Using widget within a formula is no problem...(widget*2=100)... however if I have the word widget in a drop down list I cant seem to get the formula to work. I'm guessing that in a drop down list the word widget is text and is not recognised as the the the word widget in my name manager.

Im guessing the solution is simple but I have not been able to find one..

Thanks in advance for any suggestions and help


Cheers
Henry

[Richard Buttrey]

Hi, and welcome to the forum

Yes, your analysis is right. The stuff in a Data validation list is essentially text or numbers. I'm not aware that you can use a named range in the way you think.

In any case how would you incorporate the mutiplier factor, and if you want the cell to take the value of the range name why not just enter the value (or the range name) in the first place

[henryjohn]

Thanks Richard for your response,
Im not sure what you mean by "mutiplier factor".

Im playing around with some finance calcs and mortgages...nothing to serious just playing and learning.
I was trying to have a drop down box called "Payment" with the options of Annually, Bi annual, Quarterly, Monthly, Bi Monthly, Fortnight, Weekly and Daily and depending on which one was picked the
spreadsheet would calculate the correct result.
I have a similar drop down box for "Interest compounding" with the same options.

In the name manager I set a value for each option eg Monthly=12 and Quarterly=4 and so on.

Then I was trying to have two drop down lists for the user to pick payment frequency and interest compounding frequency and then the calculations would happen.
But as you have pointed out the word "Monthly" in a pick list is different to the work "Monthly" in a name manager.

I have a similar drop down box for "Interest compounding" with the same options.
I know i can use vlookups to get the result but I was curious whether I could do it another way without have data saved elsewhere within the spreadsheet..

I hope this make sense....

Regards
Henry

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.