Any Excel experts out there?

wippers

wippers

Suspended / Banned
Messages
2,534
Name
Gareth
Edit My Images
Yes
A bit of a stab in the dark really, but I'm trying to do an Excel spreadsheet at work to make costing individual items easier and quicker. I'm getting there slowly as I'm no expert on Excel, but have come to a bit of a standstill and hope someone, somewhere may be able to help with the following:

I have a cell that has a drop down list containing various fabrics we use. I want to put a value to each fabric so that I can provide a different final price depending on which fabric is selected from the drop down box.

Can this be done? :shrug:
Does anyone actually understand what I'm on about? :thinking:

Thanks for looking:thumbs:

Gareth
 
wippers said:
A bit of a stab in the dark really, but I'm trying to do an Excel spreadsheet at work to make costing individual items easier and quicker. I'm getting there slowly as I'm no expert on Excel, but have come to a bit of a standstill and hope someone, somewhere may be able to help with the following:

I have a cell that has a drop down list containing various fabrics we use. I want to put a value to each fabric so that I can provide a different final price depending on which fabric is selected from the drop down box.

Can this be done? :shrug:
Does anyone actually understand what I'm on about? :thinking:

Thanks for looking:thumbs:

Gareth
Click to expand...


[No EXPERT}

but I'd be inclined to use the Vlookup function:

Where your chosen fabric is in column A

Your Vlookup formula will be in column B

you will require a table (maybe in another worksheet) showing the list of Fabrics , with the cost/value in a column to the right....

Copy this formula down as far as you like - Follow the Vlookup wizard and you shouldn't go far wrong....

you can then put various formulae/calculations in columns C onwards.........

Phil
 
Cheers Phil - I am trying to put the actual list on sheet 2 of the worksheet, but it will only allow me to select from sheet one when linking the data validation cell with the drop down.

Any ideas?
 
wippers said:
Cheers Phil - I am trying to put the actual list on sheet 2 of the worksheet, but it will only allow me to select from sheet one when linking the data validation cell with the drop down.

Any ideas?
Click to expand...

Wanna email me a simple version ?

just the list with some makeup prices ?

I'll insert the formula for you - see if it's any use ?

Phil
 
I've replied to the 2 spreadsheets.....

I'm off home now :D

I'll check back in later

Phil
 
If you want to use a list on another sheet tab for data validation then you will need to define a named range. If it's Excel 97-2003 that's being used, then this can be done by going to Insert -> Name -> Define and then putting the name of your list at the top and then putting the cell address/range that this name will refer to. Then, in your original cell's data validation, select List and then you can put ={your named range}, so if it was called MyValues, then the list would be =MyValues.

In Excel 2007+, you define a named range by selecting Name Manager from the appropriate ribbon. Same rules apply after that.
 
Just tried it and Excel 2010 allows a list on another sheet without having to define a named range - not that that helps the OP!
 
If you are still stuck, send me an example and I'll have a look ;)
 
The old trick to avoid the problem of not being able to use data validation on lists from another sheet is to us a section of the current worksheet that's away from your data, setup the data validation using that, then hide the columns you've used.
Not that defined/named ranges are difficult, it's just that not everyone knows how to use them.

Of course the really cool way to do this would be using VBA and a dropdown object that you can populate any way you like... but that's a bit harder than using data validation.
 
Thanks for all the info guys. Legacy has helped me massively in sorting this. I'm pretty much there now thankfully. And I thought I knew a bit about Excel. How wrong was I. :bonk:

Once again, thanks for all the input.

I may be back......:D

Gareth
 
You must log in or register to reply here.
Back
Top