Forum Discussion
Trying to create 1 formula to use in dozens of tables
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."
mathetes Thank you!
I like to use tables because when they work properly, I can add rows or columns to them without the rest of the items on a spreadsheet being impacted. I can make just the table bigger by one row. I also like that I can slice a table if I need to go back and figure out what a person's holdings were as of a particular day.
I will have to go and review the link you posted to learn more on lookups. If you still have the spreadsheet i initially uploaded, try and add rows to the tables on it and you'll see what I mean. Some of the tables it works .. some of them it doesn't, and then I have to insert a row vs. inserting a table row.
The holdings are actually public information if somebody wanted to review the proxy or our edgar filings, but I have removed the file because it was a very good point you made!!
Tracking insider holdings has always been a manual process at the various companies I've done it, but I've been on the quest to build a perfect tracker for years. Heck, I'd pay a lot of money to buy a perfect tracker if such a thing existed!!