I have an excel file that has 2 sheets. 1 for tracking services rendered and another with a list of customers and their fees per service.
Tracking Sheet
For each service, we manually enter a number for how many times the service was rendered. We then select the client from a drop-down list that we performed the services for.
Clients Sheet
To get the "Amount Scheduled to Bill" and "Total Billed" in the tracking sheet, we reference the client sheet with pricing. This will have a list of clients along with cost for each service.
Let’s say that these are the 2 formulas that will be used to calculate the following on the tracking sheet (can probably be better and if so pls share):
Amount scheduled to be billed - (Used to calculate result for Amount Scheduled)
=SUM(Tracking!A2*Clients!B2, Tracking!B2*Clients!C2)
Total Billed - (Used to calculate result for Total Billed)
=SUM(Tracking!B2* Clients!C2, Tracking!C2* Clients!D2, Tracking!D2* Clients!E2)
Question:
Per row on the tracking sheet, when entering the amount of times a service was provided, how can we correlate the calculation formula per client in the drop-down to get the amount scheduled and the amount billed?
Thank you!
Tracking Sheet
For each service, we manually enter a number for how many times the service was rendered. We then select the client from a drop-down list that we performed the services for.
Clients Sheet
To get the "Amount Scheduled to Bill" and "Total Billed" in the tracking sheet, we reference the client sheet with pricing. This will have a list of clients along with cost for each service.
Let’s say that these are the 2 formulas that will be used to calculate the following on the tracking sheet (can probably be better and if so pls share):
Amount scheduled to be billed - (Used to calculate result for Amount Scheduled)
=SUM(Tracking!A2*Clients!B2, Tracking!B2*Clients!C2)
Total Billed - (Used to calculate result for Total Billed)
=SUM(Tracking!B2* Clients!C2, Tracking!C2* Clients!D2, Tracking!D2* Clients!E2)
Question:
Per row on the tracking sheet, when entering the amount of times a service was provided, how can we correlate the calculation formula per client in the drop-down to get the amount scheduled and the amount billed?
Thank you!
