excel tables

[geo]

Hi,

I have a workbook with a two worksheets.

Worksheet1 is like a parts table it has a listing of parts (windows)

Window item, Cost of window, cost of screen, cost of grill.



Worksheet2 is like a purchase order.

I want to be able to select items from the parts table in my purchase
order? I'm not real sure the best way to do that?

Thanks,
Geo

 

[Bernard Liengme]

Use Date Validate -> List to select the item
Use VLOOKUP to get costs
best wishes

 

[geo]

I don't think Data Validate will work. I'm not able to pull the data from
worksheet1 to worksheet2 (the purchase order) using data validate unless it
was all on the same sheet.

Worksheet 1

UNIT COST SCREEN GRILLES
2535 $ 130.46 $ 14.63 $ 19.10
2541 $ 144.01 $ 16.00 $ 19.10
2559 $ 154.65 $ 20.11 $ 19.10
2941 $ 163.04 $ 16.92 $ 28.04
2965 $ 177.23 $ 22.40 $ 41.44
3341 $ 142.07 $ 17.83 $ 28.04
359 $ 132.72 $ 21.94 $ 28.04
3365 $ 246.27 $ 23.31 $ 41.44
3371 $ 259.81 $ 24.68 $ 41.44
3747 $ 155.5 $ 20.11 $ 36.98
3757 $ 165.30 $ 22.40 $ 36.98
3759 $ 227.50 $ 22.85 $ 36.98
4159 $ 214.0 N/A $ 37.03
5953 $ 234.5 N/A $ 48.56



Worksheet 2 - In this worksheet I want to be able to pick from the parts
table
enter in quanties and have the cost added up accordingly.

UNIT QTY COST QTY SCREEN QTY GRILLES LOCATION
2541 1 $ 144.01 1 $ 16.00 $ 19.10 KITCHEN









SUB TOTALS: $ 134.01 DINING ROOM
TAX: $ 11.73 KITCHEN
TOTALS: $ 145.74 LAUNDRY

 

[KC Rippstein]

Take your list on Worksheet 1 and give it a name (like PartsList). In your
data validation, type =PartsList and you will see that you can have the
parts list on the other page and still use data validation.
How sweet is that?

 

[Gord Dibben]

You can use Data Validation list from another worksheet as long as you name the
source range.

Sheet1 Unit list is named MyList through Insert>Name Define.

Sheet2 DV>.List>Source =MyList


Gord Dibben MS Excel MVP

 

[Peo Sjoblom]

If you give the list a name and then refer to the name you can use a
list from another sheet, select the list, in the name box above A1 type
the name and press enter or do insert>name>define. Once you have named
it, in the validation use allow > list and in the source box type =Name
(equal sign and the name you gave it)


Regards,

Peo Sjoblom

 

[Mark Ivey]

I uploaded an example you can look over if you like...


http://www.graceba.net/~wmivey/Book1.xls

 

[orbii]

kinda odd if you ask me. it wont' allow you to select another sheet while
making the list, but it allows you to type in another sheet's name!R:C.

anyways, if you prebuild the string =Sheet1!A1:A17 should work too

cheers, orbii

 

[Guest]

Orbii as the previous repliers mentioned you cannot link directly to another
sheet, either name the name and use the name range or use the indirect
function as:

=indirect("Sheet1!A1:A3")

 

[orbii]

hmm i'm using excel 2007, and i can... so try it

happy new year! orbii

 

[Nick Hodge]

Orbii

I have XL2007 installed and can see no difference between the functionality
of DV in 2007 from 2003. That is you cannot select or type a straightforward
range address on another sheet or in another book.

How are you achieving this?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

OK I'm still on 2002.

Point taken. But I think the user is still on a version lower than 2007.
Anyway way glad to know there is something else save for exceedingly large
files and a new menu bar.

Have a good new year.

 

[Roger Govier]

Hi Nick

I have just installed Office 2007 and have been playing with XL2007.
On sheet1 I entered some values in A1:A3
On Sheet2 I applied DV>List>=Sheet1!A1:A3
and it works perfectly.

I agree that in earlier versions this was not possible.

On another note, apart from a certain amount of frustration with finding
where things are located, my general impression is very favourable - but
perhaps it is early days yet!!!

All the very best for 2007 (the New Year not Excel!!)

 

[Nick Hodge]

Roger

I tried it with Nick typed in Sheet2!A1 and then went to a cell on Sheet1
and applied DV>List and =Sheet2!A1 and =Sheet2!$A$1 and neither
worked...very late for football, so try multi cells later

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[orbii]

i think i've moved almost everyfile this company has got excel wize over to
2007... and let me tell you... i freaking love it

happy new year! orbii

 

[Roger Govier]

Hi Nick

Using a single cell reference does come up with the message
"You cannot use references to other Worksheets or Workbooks"
but using multiple cell references works.

Good luck against Leicester - I will be watching Arsenal v Sheffield on
Sky a little later, along with my son.

 

[marc]

Thanks for all the help.

What if they don't necessarily have to choose a screen or grill. So they
can choose say 3 windows but don't have to choose a screen or a grill?
Sometimes you can select a window without a screen or grill.

 

[Mark Ivey]

This should do it...

http://www.graceba.net/~wmivey/Book2.xls