Forum Discussion
Trying to create 1 formula to use in dozens of tables
mathetes Hi!! here's the spreadsheet without names in it. it's used to track stock ownership of our section 16 officers at a large company. the page DIV is where the constant values are. (.28 cents and $52 closing price.) Every yellow cell in the various pages in the charts is where the fomulas would go.
After a quarterly dividend is paid each quarter I have to calculate what it would be for each award. Each award is its own table. Each insider has his own sheet. I then have to add up the total of the dividends for each person and report those to the SEC.
Here's a start at what I think (but I'm not sure yet) would help you. First of all (having worked in HR and HR and Payroll and Benefits systems for much of my career, prior to retiring 18 years ago)....I suspect that the .28 and $52 are in fact NOT constant. Not really constant (as in for all eternity, maybe not even for the next year, certainly not the next decade). Dividends and Closing Prices change, the latter on a daily basis, the former often on an annual basis.
They may indeed be constant for the day they're applied, but you need a way to handle them both for that kind of historical record as an accurate and permanent factor for that day in history, but you also need to have a way to handle next quarter, and the quarter after that, etc. Which means you need a table. See below:
Those numbers are all fictitious, just there for illustration. And the dates too....the first several are taken from one of your actuals, but I just added 90 days to go into the future.
With this, and the use of XLOOKUP, a new function, looking the appropriate value up would be easy. In fact, here is a formula that does it. I've entered it into not only the yellow background cells of your first two individual sheets, but the ones going back to last January. As you'll see, using the table as the basis for the lookup causes the values that were in place on those dates to continue to apply to that NewUnits column.
=[@[Starting Balance]]/XLOOKUP([@Date],DIV!$F$7:$F$14,DIV!$H$7:$H$14)*(XLOOKUP([@Date],DIV!$F$7:$F$14,DIV!$G$7:$G$14))
What gets tricky, and I'm going to leave it to you to resolve this: you've named each table, so it's actually not possible to easily copy the formula from a cell where it's working to a corresponding cell on the adjoining table for the same person on the same sheet, to say nothing of going to a different person's sheet.
The sheets look nicer as with all those "by grant" tables distinct, but if you don't actually need them as official Tables, you might be better served by making them simple arrays.
[Parenthetically, you should once again delete your sample sheet: the names of the individuals are still present in the names of the tables!! Little harder to see, but as soon as I tried to copy the formula from one table to another, there the name was, staring me in the face! I have deleted all but the first three sheets--so you can see how this works--but I'm going to delete it too as soon as I get confirmation that you've gotten the sheet I'm attaching.]