Formula with date and price linked

Balmoral / Gryphen Admin

I have 3 sheets. One is a master sheet with training expiry dates (Master Recency), then I have a training budget, monthly, and a pricing sheet. The training department updates the Master recency sheet with the dates training will expire. I need the training budget to look at the master recency sheet. Take the renewal column, put it in the applicable month, however not the date but pull the cost price from the price list and put that in the month column.

I got it to work with the below formula, but it is VERY labor intensive, and was wondering if there is an easier way.

basically, I said if the Med renewal date left 2 digits are applicable to the month on the budget, then look at the med price on the training price and put that in. Then move to DG, then move to SEPT but I have to do this for every training course and every person and every month. I cannot copy and paste.

Formula for (Nic Slabbert Jan on Training budget)

=IF(LEFT({Master Recency Data Range 4}, 2) = "01", {Training Prices Range 1}, 0) + IF(LEFT({Master Recency Data Range 5}, 2) = "01", {Training Prices Range 2}, 0) + IF(LEFT({Master Recency Data Range 7}, 2) = "01", {Training Prices Range 3}, 0) + IF(LEFT({Master Recency Data Range 9}, 2) = "01", {Training Prices Range 4}, 0) + IF(LEFT({Master Recency Data Range 11}, 2) = "01", {Training Prices Range 5}, 0) + IF(LEFT({Master Recency Data Range 13}, 2) = "01", {Training Prices Range 6}, 0) + IF(LEFT({Master Recency Data Range 15}, 2) = "01", {Training Prices Range 7}, 0) + IF(LEFT({Master Recency Data Range 17}, 2) = "01", {Training Prices Range 8}, 0) + IF(LEFT({Master Recency Data Range 19}, 2) = "01", {Training Prices Range 9}, 0) + IF(LEFT({Master Recency Data Range 21}, 2) = "01", {Training Prices Range 10}, 0) + IF(LEFT({Master Recency Data Range 23}, 2) = "01", {Training Prices Range 11}, 0) + IF(LEFT({Master Recency Data Range 25}, 2) = "01", {Training Prices Range 12}, 0) + IF(LEFT({Master Recency Data Range 27}, 2) = "01", {Training Prices Range 13}, 0) + IF(LEFT({Master Recency Data Range 29}, 2) = "01", {Training Prices Range 14}, 0) + IF(LEFT({Master Recency Data Range 31}, 2) = "01", {Training Prices Range 15}, 0) + IF(LEFT({Master Recency Data Range 33}, 2) = "01", {Training Prices Range 17} + {Training Prices Range 19} + {Training Prices Range 20}, 0) + IF(LEFT({Master Recency Data Range 8}, 2) = "01", {Training Prices Range 18+{Training Prices Range 19} + {Training Prices Range 20}, 0) + IF(LEFT({Master Recency Data Range 35}, 2) = "01", {Training Prices Range 16}, 0)

Then Feb

=IF(LEFT({Master Recency Data Range 4}, 2) = "02", {Training Prices Range 1}, 0) + IF(LEFT({Master Recency Data Range 5}, 2) = "02", {Training Prices Range 2}, 0) + IF(LEFT({Master Recency Data Range 7}, 2) = "02", {Training Prices Range 3}, 0) + IF(LEFT({Master Recency Data Range 9}, 2) = "02", {Training Prices Range 4}, 0) + IF(LEFT({Master Recency Data Range 11}, 2) = "02", {Training Prices Range 5}, 0) + IF(LEFT({Master Recency Data Range 13}, 2) = "02", {Training Prices Range 6}, 0) + IF(LEFT({Master Recency Data Range 15}, 2) = "02", {Training Prices Range 7}, 0) + IF(LEFT({Master Recency Data Range 17}, 2) = "02", {Training Prices Range 8}, 0) + IF(LEFT({Master Recency Data Range 19}, 2) = "02", {Training Prices Range 9}, 0) + IF(LEFT({Master Recency Data Range 21}, 2) = "02", {Training Prices Range 10}, 0) + IF(LEFT({Master Recency Data Range 23}, 2) = "02", {Training Prices Range 11}, 0) + IF(LEFT({Master Recency Data Range 25}, 2) = "02", {Training Prices Range 12}, 0) + IF(LEFT({Master Recency Data Range 27}, 2) = "02", {Training Prices Range 13}, 0) + IF(LEFT({Master Recency Data Range 29}, 2) = "02", {Training Prices Range 14}, 0) + IF(LEFT({Master Recency Data Range 31}, 2) = "02", {Training Prices Range 15}, +IF(LEFT({Master Recency Data Range 33}, 2) = "02", {Training Prices Range 17} + {Training Prices Range 19} + {Training Prices Range 20}, 0) + IF(LEFT({Master Recency Data Range 8}, 2) = "02", {Training Prices Range 18+{Training Prices Range 19} + {Training Prices Range 20}, 0) + IF(LEFT({Master Recency Data Range 35}, 2) = "02", {Training Prices Range 16}, 0))

Paul Newcome

A better way to word my question...

Does each entry in Sheet B have its own column in Sheet A?

Paul Newcome

What is the logic behind the 15088.5 in row 5 of the 3rd screenshot?

Balmoral / Gryphen Admin

It is the same formula that I have copied down I just have not changed all the references. That specific course takes into account three cells on the training price list..

Oh and I did fix the bracket on training price 18 just FYI

=IF(LEFT({Master Recency Data Range 4}, 2) = "11", {Training Prices Range 1}, 0) + IF(LEFT({Master Recency Data Range 5}, 2) = "11", {Training Prices Range 2}, 0) + IF(LEFT({Master Recency Data Range 7}, 2) = "11", {Training Prices Range 3}, 0) + IF(LEFT({Master Recency Data Range 9}, 2) = "11", {Training Prices Range 4}, 0) + IF(LEFT({Master Recency Data Range 11}, 2) = "11", {Training Prices Range 5}, 0) + IF(LEFT({Master Recency Data Range 13}, 2) = "11", {Training Prices Range 6}, 0) + IF(LEFT({Master Recency Data Range 15}, 2) = "11", {Training Prices Range 7}, 0) + IF(LEFT({Master Recency Data Range 17}, 2) = "11", {Training Prices Range 8}, 0) + IF(LEFT({Master Recency Data Range 19}, 2) = "11", {Training Prices Range 9}, 0) + IF(LEFT({Master Recency Data Range 21}, 2) = "11", {Training Prices Range 10}, 0) + IF(LEFT({Master Recency Data Range 23}, 2) = "11", {Training Prices Range 11}, 0) + IF(LEFT({Master Recency Data Range 25}, 2) = "11", {Training Prices Range 12}, 0) + IF(LEFT({Master Recency Data Range 27}, 2) = "11", {Training Prices Range 13}, 0) + IF(LEFT({Master Recency Data Range 29}, 2) = "11", {Training Prices Range 14}, 0) + IF(LEFT({Master Recency Data Range 31}, 2) = "11", {Training Prices Range 15}, +IF(LEFT({Master Recency Data Range 33}, 2) = "11", {Training Prices Range 17} + {Training Prices Range 19} + {Training Prices Range 20}, 0) + IF(LEFT({Master Recency Data Range 8}, 2) = "11", {Training Prices Range 18} + {Training Prices Range 19} + {Training Prices Range 20}, 0) + IF(LEFT({Master Recency Data Range 35}, 2) = "11", {Training Prices Range 16}, 0))

Paul Newcome

Are you able to break it down into "plain English"? Without knowing what the different ranges are, it is hard to tell exactly what is going on in your formula.

Based on your 1st screenshot, Srinath has 4 that are expired. If you add those 4 up referencing the 2nd screenshot, the total is $389.28. Your 3rd screenshot has a total of 15088.5. I also don't understand why that cost is in every month of the 3rd screenshot instead of a single month.

Balmoral / Gryphen Admin

Right because when I got to Srinath I thought I would ask as there has to be an easier way and I did not finish the formula.

ok, this is what I want.

Nic and Sri both have Medicals expiring in March, so I want the budget sheet to read $89.29 +$89.29 in Feb (which is the renewal month) there should also be an $89.29 in June for Wimpie, August for Eben, and Sep for Wouter.

Then moving on to DG there should be added to the cost of medicals in each month (if applicable) the cost of any expiring DG, thus Srinath will need a DG in Oct (I know it is 2020 now but it will change to 22 soon) and Hayden needs a DG in November so $107.14 needs to be added to whatever is in those months already.

Then you move on to CRM each renewal month should be allocated to the month on the budget

So to summarize on the budget I want the below in $ value automatically filled based on when the staff needs to take there their tests so that I can have those funds allocated on my cash flow.

02/21 = Med+Med

06/21 = Med

07/21

08/21 = Med+CRM+SEPT

09/21 = MED+SEPT

10/21 = SEPT

Does that make sense?

Paul Newcome

I want to make sure I understand correctly...

Working down the 3rd screenshot...

Nic should have $0.00 for January, $89.29 for February because of a single renewal in the Medical column, and $0.00 for March.

Ryan will have $0.00 for all 3 months because there are no renewals scheduled.

Srinath will have $0.00 for January, $89.29 in February for the Medical renewal, and $0.00 for the MArch column.

If someone had Medical and DG renewals in the same month, that month's column would be populated with $196.43.

Does it sound like I have a grasp on what you are wanting to automate in that 3rd screenshot?

Balmoral / Gryphen Admin

That is exactly right!

Paul Newcome

Great. And will this just be for current year, or would you want (for example) October amounts to include October of 2021 and 2022?

Balmoral / Gryphen Admin

Ideally, I would like to have 2021 in 2021 and 2022 in 2022 as I project the budgets forward

Paul Newcome

Is it just those 4 renewal columns, or do you have a column in Sheet A for every entry in Sheet B?

Balmoral / Gryphen Admin

Sheet A actually has columns that sheet B does not have as we do in-house training which is free. I also do not use both in-person and online prices, we are defaulting to online prices and only do in-person where online is not available

Balmoral / Gryphen Admin

I could make them match if it would help simplify

Paul Newcome

A better way to word my question...

Does each entry in Sheet B have its own column in Sheet A?

Balmoral / Gryphen Admin

Yes it does

Balmoral / Gryphen Admin

https://community.smartsheet.com/discussion/comment/293290#Comment_293290

I think I might have closed this by accident or not I am not sure 😂

Paul Newcome

Ok. It is still going to end up being a lengthy formula with some data "hard coded" in. Basically we count the number of times the corresponding month/year combo shows up in the Medical column of Sheet A and then multiply that by the appropriate value in Sheet B.

So for Medical in Feb of 2021 you have:

=COUNTIFS({Source Data Name}, @cell = Name@row, {Source Data Med}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021)) * INDEX({Cost List $ Conv}, MATCH("Medical", {Cost List Training}, 0))

Then you would duplicate that formula and change "Medical" to the next option and add that on:

=COUNTIFS({Source Data Name}, @cell = Name@row, {Source Data Med}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021)) * INDEX({Cost List $ Conv}, MATCH("Medical", {Cost List Training}, 0)) + COUNTIFS({Source Data Name}, @cell = Name@row, {Source Data DG Online}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021)) * INDEX({Cost List $ Conv}, MATCH("DG Onlne", {Cost List Training}, 0))

So on and so forth until you have covered each of the different trainings. I suggest keeping column names and the names of your cross sheet references as short as possible because there is a 4,000 character limit (including spaces) per cell.

Once you have that built out for each option for Feb 2021, you will need to adjust the month and year numbers throughout the formula to reference each appropriate month/year.

=COUNTIFS({Source Data Name}, @cell = Name@row, {Source Data Med}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021)) * INDEX({Cost List $ Conv}, MATCH("Medical", {Cost List Training}, 0)) + COUNTIFS({Source Data Name}, @cell = Name@row, {Source Data DG Online}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2021)) * INDEX({Cost List $ Conv}, MATCH("DG Onlne", {Cost List Training}, 0))

The benefit to this would be that you can adjust prices in your cost list without having to adjust the formula.

My personal suggestion would be to break it down into sections. So you have a single set of names for medical. Then you duplicate this set of names for DG Online, etc. Then you have a section that sums each of the sub-sections for each person.

If you go this route, you can use parent/child rows with cell references to make copy/paste of formulas much more simple so that you can create each section with minimal editing. If you are interested in going this route, let me know, and I will throw together a quick sample for you.