Excel Clarification

could you please advise of the attached template.
1. i have multiple vendors ,where if i select one vendor with either product or warranty , corresponding discount number should highlight as mentioned
2. IF i add price , lets say USD , how can i automate to convert to aed
3. similarly for point 1 , i need to mentioned, if it is warranty, there is no customs charge to be added, rest it would be as per table

Could you please advise and assist as per template.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:

Looking at your data i'm not sure where you want the results to be populated. If you can provide me a more context about which sheet you want the results to be populated and what results you are expecting. It will be faster to design for a solution for you accordingly..

infiniti7181Author Commented:
Hi thanks for the reply

i was looking something like as attached .

1 LEts say i have two vendors , Cisco , Juniper , each has product and warranty, when i select through dropdown Cisco + Product  i should get corresponding discount value , similarly if i select Juniper + warranty , i should get corresponding discount value , so there will be two cells , ie Vendor , Subgroup etc

2. Secondly In the first sheet , i know how to make vertical dropdown list, but how can i make dropdo
wnlist for horizontal section , as seen in sheet3 , there is product and warranty horizontally placed

3. when i enter price and select currency , it should automatically covert to aed , eg: if i enter value 10 in unit price and select currency usd, it should convert to aed automatically. also when product is selected , it should be multipled with 1.0X as mentioned in the sheet .
Saurabh Singh TeotiaCommented:
I believe this is what you are looking for...

Now converting it to AED i need to have the conversion rate for each currency which you want to use and then accordingly i can set up a formula for the same..So if you can provide me the conversion rate i can set the rest of the formula for you...

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think you need a VBA solution to achieve what you are asking because you cannot replace a manual input by a formula or vice versa.

Please find the attached workbook where a drop down in col. C has been added to select SubGroup.
After downloading the file, don't forget to enable the macros/editing/contents while prompted when opening the file first time.
Follow these steps to test the code.

1) Select Vendor in col. B.

2) Select SubGroup in col. C.

3) Input Qty in col. F.

4) Input Unit Price in col. G.

5) Formula to calculate the Total Price in col. H is already in place.

6) Discount% will be calculated as per the previous formula in col. I.

7) Now select the currency type in col. J and once you do so, the Unit Price will be automatically converted to AED.

8) The lookup table for currency conversion is on Sheet3.
infiniti7181Author Commented:
Thanks Bro's .. I see some automations in it .

for exchange it is

1 USD = 3.67 AED
1GBP=5.67 AED
1 EURO = 4.65 AED

may i know some clarifications

1. Relateed with the currency, what i require is , if i enter a value in unit price and select currency , coversion should take place and i beleive this is what the formula does right .

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Change the currency values on Sheet3 in the range L2:L4.
Also if you input 10 in unit price in col. G and select USD in col. J drop down, the unit price will be converted to AED currency rate i.e. 36.70.

I have entered the values for one row. You may input values in another row to test it.

I assume this is what you are trying to achieve.

See the attached.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
You can do something like this to do conversion...

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:

One of your previous question has been re opened by the moderator, please re assign the points by accepting the solutions.

The link to your previous question is this......

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.