file:Dropdown lists

Hi,

I have a question that has caused me big confusion in my Excel files. I need to update one of my tabs in this excel sheet so that it can read from a dropdown list. Below is the example on how I want it.

Sheet 2, I have an issue note for stock that I need to fill out with ammounts so that I can send as an email attachment once a week or daily.

The stock names of this issue are selected from an already created drop-down list
Ex: item1, item2, item3 all in a dropdown list and you can select these dropdowns of these names in cells D6 to cell D18.

Now in cell H6 to H18, the ammount of items need to be written down for the item selected from the drop down list.

Ex: item1 (from dropdown in cell D6) 400 (written in cell H6)
item2 (from dropdown in cell D7) 100 (written in cell H7)
item3 (from dropdown in cell D8) 5 (written in cell H8)

When these items are selected and updated with the ammounts needed, it is sent by email for ordering on that particular date.

In sheet 1 on same excel file, I have a list of the stock items same as items in dropdown list on cells A2 to A4 (not as a dropdown list).
Ex: item1(A2)
item2(A3)
item3(A4)

Now, here is the trouble I'm having. I need a sort of a formula or something that automatically writes down the ammount written on sheet 2 on cells H6 to H18 (in this example, 400, 100 and the 5) to their respective item in sheet 1 and the column of that particular date.

Ex: item1(A2) 400(B2)
item2(A3) 100(C3)
item3(A8) 5(E8)

Originally Posted by NBVC

Try Vlookup

=Vlookup(A2,'Sheet1'!$D$6:$H$18,2,FALSE)

adjust as necessary

But will it pick my items from the dropdown list, Like that?

Yes I did the drop down list from Data Validation and the criteria was entered as list and I made them as in-cell dropdown and the source is =$L$5:$L$60

I copied these dropdowns in D6 to cell D18.

Originally Posted by NBVC

I think it would be easier to follow if you could post a sample workbook

check this sample file, please

Originally Posted by NBVC

You should first ensure that the numbers entered in column H of each sheet is formatted as numbers and not text

Then you can use formula:

=SUMIF(INDIRECT("'Mr X Issue "&B$1&"'!$D$6:$D$18"),$A2,INDIRECT("'Mr X Issue "&B$1&"'!$H$6:H$18")) in B2 and copied down and across the table.

Sample shows som REF errors, because those referenced sheets not in your sample

Ur a marvellous genius........... It worked 100%

The formula reads the dates from the tab name is it? Can it read from the date in the sheet?

"Mr X issue" is a name I'm giving to all the new tabs I create along with the date and I thought it was reading from there, but never mind it doesn't bother me.

thanks very much

with these tabs created each day in this whole file, can I send only one tab that I select through an email or do I have to send all the time the whole file?

Originally Posted by NBVC

It reads from the text you have in C1:Q1 of the items monthly sheet and concatenates it to the text "Mr X Issue ".

Not sure what you are asking specifically?

with these tabs created each day in this whole file, can I send only one tab that I select through an email or do I have to send all the time the whole file?

Originally Posted by alancauchi

with these tabs created each day in this whole file, can I send only one tab that I select through an email or do I have to send all the time the whole file?

First of all I hope you had a wonderful Christmas and I hope you Have a Happy New Year.

About sending only one tab from that whole file, I asked around and it's not possible as I did with Excel 2003.

Another thing that I need to do is to sum up all the items in that row for a whole year, but since there are the REF errors on that row, it's giving me a value of REF also.

Can something be done please?