Forum Discussion
Trying to create 1 formula to use in dozens of tables
I have a couple questions for you. I should add before going on that it probably is fairly easy to do what you're asking, but your brief description leaves a LOT that is unclear, so the precise way to accomplish what your asking will depend on getting greater clarity.
So here we go with some questions. It might help, if you just jump to the last question; the preceding ones may become moot if you can comply with the last.
- Why multiple tables? Given that you're dealing with dividends, are these all tables of financials of different companies/stocks?
- Are the multiple tables all on one spreadsheet, or each on its own sheet?
- The absolute reference of 0.28...is it an absolute reference (as in $A$1) or just a constant, not a variable?
- Same question for the other that you describe as an absolute reference, the dollar figure.
- Are you familiar with named ranges? If the 0.28 is in fact a constant, you could name the cell that contains it as, say "Rate" and then just incorporate the word Rate in your formulas. Anytime that rate changed, you'd just change it once, in the base cell, and each and every formula that contained it would automatically be up to date.
- Are you familiar with VLOOKUP and various variations on that (HLOOKUP, XLOOKUP, INDEX & MATCH)?....those could help you with a variety of variables that apply across various tables.
- Is it possible for you to post a copy of your actual spreadsheet?
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.]