This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi there. Probably a simple question compared to some of the others I have seen on here, but I am trying to replicate something similar to the excel spreadsheet above. The expense, income, and net change data is there. However, I need to create the opening, generation(utilization), and closing columns.
The 2018 opening is the only number given. Generation/Utilization is the smaller of opening or absolute value of net change). Closing is open + generation/utilization. And then next year's opening is just prior row's closing.
Is there a simple way to do this? I've been trying to use multiple columns and multi-row/field formulas to no success. Thank you!
I've whipped together a solution that first Transposes the data, then uses the Multi-row formula to generate the values, before CrossTabbing back to the original form.
I essentially look for the value of the "name" field that I want to update, and set the value based on position relative to the others. If your columns are not always in the same position, you may want to sort before applying the multirow.
You'll also probably need to tweak the row numbers a bit, as I didn't include the Expense or Income fields, but this will give you the gist of it.
Let me know if this works for you, or you have any questions: