Forum Discussion
Trying to create 1 formula to use in dozens of tables
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.
mathetes Thanks - you've gone a little over my head with the array and VLOOKUP stuff. Would you not name your tables, ever?
Some of the tables don't function properly - they'll tell me I can't add row because it'll move cells on another table, which makes no sense, so I could recreate this in a file where I don't name any tables. The point of the tables was to be able to add rows without impacting everything else on the sheet.
The dividend number does always stay the same at 0.28 cents, but the closing price changes. What I meant was for each quarter, I'm referencing the same cents and closing price. Next quarter it's a different closing price, but the 28 cents remains.
This is kind of fun!
I used XLOOKUP...here's a link to an instructional website that will help you get a handle on that. https://exceljet.net/excel-functions/excel-xlookup-function
Would I not ever name a table? Actually when you create an official Excel Table (with the automatic coloring of alternating rows, etc) it is assigned a name whether or not you give it one. If you don't assign a name, as you've done, Excel in its uniquely creative way calls them Table1, Table2, and so on. Normally it's not a problem, but in your case, it does interfere with getting a formula like the one I gave you and just doing a copy&paste operation to put it everywhere. On the other hand, tables have their advantages, too.
By referring to arrays, I simply meant you could arrange the data in rows and columns just as you have it, but don't do the "Insert...Table" step (assuming that's how you've done it. You could use color backgrounds to differentiate between award numbers. Without seeing what you're describing with the difficulty in adding rows to one without affecting another, I don't have any advice.
My table, the one accessed by the XLOOKUP function can work with an unchanging dividend--just keep it the same--even while you change the closing price. But I'd keep the option open to have a different dividend, because that kind of thing DOES change occasionally.
I'm glad you're finding it fun. It's the reason I keep coming here to help folks like you. I'm 79, happily retired, but I love learning more and more when challenged to solve some of the problems that you and others bring here. Creating an elegant Excel spreadsheet is, to me, a LOT more fun that a video game. And the more you dig in, the more fun it can become. Excel is remarkably powerful.
Please, by the way, don't forget to go back to that last copy of your spreadsheet that you posted and take it down. As I said, it does contain names, just "off screen."
