Hi guys, just needing a formula for the spreadsheet I have added a screenshot to above. Essentially, the spreadsheet has got to remain as it is in terms of layout and number of columns if possible (I'm making this spreadsheet for others to use and I want it to be as simple and user friendly as possible and let the formulas do the bulk of the work.
So essentially what we've got in the spreadsheet is first of all the facilities column which identifies individual facilities (selected from a drop down list). Then there is an addition/transfer/total column. How I envision this working is essentially the units as we shall call the are tallied once a month from that are delivered from an external source. This is only looking at monthly totals. These deliveries are classified as an "addition". With most of the facilities, the "total" units for the month will be identical to the "addition" value for that site as those units will be used by that facility. However, smaller facilities have internal transfers ("transfers") which is a deduction from a larger facility. For instance, for the month of February facility four had an addition of 80 units, however it had a transfer of 10 units to facility two. In the spreadsheet, this will show as: addition 80 units facility four, 10 units transfer from facility four to facility two as shown in table. I would also like that 10 units to be able to populate the addition for facility four for february from 0 to 10 units. Then in the total it will show as 70 units facility four and 10 units facility two for the month of february. The totals, additions and transfers (deductions as well as additions) will all show in the table on separate rows as is differentiated by their type.
I think the solution is a very complex or nested if (sumif?) statement in the values columns for the months (starting in f3) that will first lookup the facility name in the facility column for matching facilities, then it will move over to the addition/transfer/total column and do a lookup in that column. Based off what is in that column it will populate the total and possibly the addition values for the month for that facility as well. If it is an addition in the second column it will become the total (facility total = addition - transfers (if there are any for that facility otherwise facility total = addition). If there is a transfer for that facility the value will be subtracted from the addition (but only for the purposes of the total, the addition value for the facility being transferred from will remain the same). However, it would be nice to add the transfer value as an addition to the facility that the units are being transferred to (as shown in the blue font in screenshot). Then, from there additions = total so it is straightforward This will need an if statement too i Imagine based off the transfer facility column? As shown in the table, there is also the possibility for multiple transfers from facilities however, there will only be one addition and one total row for each site.
The total value will also be calculated by being equal to additions - transfers (if they exist) for that month only. The inputs will only be additions or transfers, totals will not have a input but will be calculated as mentioned prior.
Really curious to hear your suggestions, let us know if anything is unclear. I wouldn't rule out doing hidden columns if need be as I think the formula that will need to be inputted into the F3 cell will be quite complex unless another function/s is more suited for this purpose? Blue font is what I would like the formula to be able to complete for me and black font is data entry. Red font is transfers/deductions
