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!
