Forum Discussion
Trying to create 1 formula to use in dozens of tables
Hello - I have a large spreadsheet full of tables. I would like to create one formula that I can apply to all of them.
This formula would multiply a cell in a table by an absolute reference on another sheet (say 0.28) and then divided by another absolute reference which can also be on the same sheet as the reference above ($52, as an example.) I manually calculate this (which is a dividend) on each and every table ... but it seems to me that 1 formula could be written and applied to every table.
Anybody have any ideas?
11 Replies
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.]
Select the cell with the multiplication factor.
Click in the name box on the left hand side of the formula bar.
Specify a name, for example Factor, then press Enter.
Select the cell with the fixed amount.
Click in the name box on the left hand side of the formula bar.
Specify a name, for example Amount, then press Enter.
You can now simplify the formula to =C2*Factor/Amount where C2 is the cell in a table you want to refer to.
If the cell you want to refer to is in a column with the same name in all those tables, say Sales, the formula can be the same in all rows of all tables:
=[@Sales]*Factor/Amount
HansVogelaar This is great! The one thing it's doing, however, is pulling in the table name each time.
=[@[Starting Balance]]*Dividend/ClosingPrice works perfectly! When i paste it into a new table, however, the table name shows up: =TRSU15605[@[Starting Balance]]*Dividend/ClosingPrice
If I remove the table name (TRSU15605) it works. If it don't remove the table name, it says #VALUE!. I then copy the formula into another table and have to manually go and remove the new table name that shows up. Seems like I'm missing something important!
You responded to @Hans Vogelaar with this comment,
If I remove the table name (TRSU15605) it works. If it don't remove the table name, it says #VALUE!. I then copy the formula into another table and have to manually go and remove the new table name that shows up. Seems like I'm missing something important!
and it reflects exactly what I experienced. It's because you have named tables, at a very granular level. There's a tricky way around it, which is to copy the formula NOT from one cell on one table to another table, but copy the formula itself--from the formula bar at the top of the screen--and paste it. You're then copying the text of the formula, rather than the formula with all its relative and absolute references which get adjusted when you go to the new...and that formula itself has embedded in it the table names.
Anyway, my formula uses XLOOKUP and a table to get the changing values of dividend and closing prices for the date corresponding to the transaction.
