Ok I have what I think is a difficult task in front of me with excel, I will try my best to describe what I need it to do on the worksheet..
Worksheet 1 (ws1) contains dropdowns (meds) (size of bottle)
Worksheet 2 (ws2) contains inventory names of the of the meds and total in stock of each size of bottle (this is not used to make the lists for the drop downs in ws1)
Here is what I need it to do..
I select a med form drop down and size of bottle in WS1 and enter in the amount used.
I now need Med name in WS1 (from dropdown) to find the corresponding Med name in WS2 along with the container size and subtract usage from appropriate cell in WS2. clear as mud??
Example: I enter in a usage of pulsatilla out of a 250ml bottle that would need to update cell m7 and I use ipeca out of 30ml bottle that will need to update cell d4 (On WS1 that would be seperate rows)
I am thinking I should use the list in the WS2 for both the med name and the bottle size to make the dropdown lists in WS1 instead of a separate worksheet for both.
I know I should be using a database but my buddy knows how to use excel.
Any help with the function that would get this done would be great
Thanks in advance
Worksheet 1 (ws1) contains dropdowns (meds) (size of bottle)
Worksheet 2 (ws2) contains inventory names of the of the meds and total in stock of each size of bottle (this is not used to make the lists for the drop downs in ws1)
Here is what I need it to do..
I select a med form drop down and size of bottle in WS1 and enter in the amount used.
I now need Med name in WS1 (from dropdown) to find the corresponding Med name in WS2 along with the container size and subtract usage from appropriate cell in WS2. clear as mud??
Example: I enter in a usage of pulsatilla out of a 250ml bottle that would need to update cell m7 and I use ipeca out of 30ml bottle that will need to update cell d4 (On WS1 that would be seperate rows)
I am thinking I should use the list in the WS2 for both the med name and the bottle size to make the dropdown lists in WS1 instead of a separate worksheet for both.
I know I should be using a database but my buddy knows how to use excel.
Any help with the function that would get this done would be great
Thanks in advance