Filling columns based on multiple fields

[TiffanyH12]

Hi! So I'm trying to create columns that fill based on two criteria: FY and useful life. Basically, at FY__ with __ useful lives, the ratio would input in a column that starts at that FY.

I have a table that breaks down the ratios based on the useful life. The ratios are all the same depending on the # of years and need to be input in the same order

Essentially the output should look similar to this:

FY	Useful Life	2015	2016	2017	2018	2019	2020	2021	2022	2023
FY 15	3	0.166666667	0.333333333	0.333333333	0.166666667
FY 17	3			0.166666667	0.333333333	0.333333333	0.166666667
FY 18	5				0.1	0.2	0.2	0.2	0.2	0.1
FY 16	3		0.166666667	0.333333333	0.333333333	0.166666667

[BrandonB]

Wasn't sure what you were using for the ratios, but I think that this workflow should get you close! Just need to add in the piece that does the ratio calculation. Workflow is attached. 

 

[TiffanyH12]

Thanks Brandon! This is similar to what I'm trying to accomplish. I have the ratios calculated in a separate sheet (shown at the bottom). I'm guessing I'll have to join the ratio's sheet in somehow. 

How would you incorporate this information into your WF?

Years
3	0.166666667	0.333333333	0.333333333	0.166666667
5	0.1	0.2	0.2	0.2	0.2	0.1

[BrandonB · Accepted solution]

Just a little transformation of the ratio table down below using a transpose and then joining it back into the data. Then when it is cross tabbed you should see all of the ratios populate the cells as expected. 

 

[TiffanyH12]

Thank you!!! This did exactly what I needed