Hi everyone!
I would be interested whether it is possible to do the following. My company is providing different type of services and I am responsible for creating invoices. Each service type has its own code (code: ServiceA, ServiceB and ServiceC), and then if the customer consumed the predefined amount, we charge them just the monthly charge, or if they used more or less then we align this on the invoices the correct way.
We have 3 codes for each service type:
1.) monthly charge code
2.) over consumption charge code
3.) under consumption charge code
For this example I pick 3 service types. Let's name them: ServiceA, ServiceB and ServiceC. You have to know that under each service type the charge code is different. So in all we have 3 (service type) x 3 (charge code for each service type) = 9 charge codes.
[TABLE="width: 500"]
[tr][td][/td][td]ServiceA
[/td][td]ServiceB
[/td][td]ServiceC
[/td][/tr][tr][td]monthly charge code
[/td][td]A1M
[/td][td]B1M
[/td][td]C1M
[/td][/tr][tr][td]overconsumption
[/td][td]A1O
[/td][td]B1O
[/td][td]C1O
[/td][/tr][tr][td]underconcumption
[/td][td]A1U
[/td][td]B1U
[/td][td]C1U
[/td][/tr]
[/TABLE]
In my excel file I have a data validation, where I first select the service type, then I select what type of billing I want to process. It looks like this:
[TABLE="width: 500"]
[tr][td]ServiceA
[/td][td]monthly charge code
[/td][td]xxx
[/td][/tr]
[/TABLE]
What I want to achieve with excel is to have excel first, select what types of services we are talking about, then from this range select the correct charge code based on the consumption and fill it automatically in the cell (xxx).
I am looking forward to your replies. Thank you!