Trying to create 1 formula to use in dozens of tables

[leanne_michels]

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?

[#2]

@leanne_michels 

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

[#3]

@leanne_michels 

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?

[#4]

@Hans Vogelaar   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!

[#5]

@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.

[#6]

@leanne_michels 

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.]

[#7]

@leanne_michels 

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.

[#8]

@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! 

[#9]

@leanne_michels 

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."

[#10]

@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!!

[#11]

@leanne_michels 

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!!

On principle, though, it's best not to just leave payroll reports lying around, don't you agree. Yes, the top execs do have a lot out there in edgar filings, but ....

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!!

Are  you a consultant, working with and for several companies? 

I'm sure that a really good tracker could be built. Perfect? That might be a tall order.

I'm also pretty sure that such a tracker would have a major transactional database and only a few business tables involved.

• By "business table" I mean something like the little date based table I created...something that has inherent in it some "instructional" considerations for functions to be performed on the raw transactional data. Another business table might take all the awards, their dates of granting, dates of vesting, etc. That business table could drive how each exec acquires their additional shares as time rolls on.
• By "major database," I mean, in fact, a very large table that would--conceptually at least--combine all the information on all those executives and all the individual actions/transactions regarding their awards, dividends, cash in and cash out, into a single table.

One of the mistakes that many people make when first getting in to Excel is to break things apart, as you've done. It's in some ways easier to think of it that way, probably mirrors how we'd do it if we kept these records on paper. But the truth is that Excel can take a well-designed single database that tracks all the data on transactions and break it apart, dealing with individual rows based on such things as SSN and award number, calendar dates...Excel can take a single table and do wonders.

We actually make it harder for Excel to "show its stuff," as you are experiencing, when we break the raw data apart to make it easier for ourselves.

Add tags

[#12]

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!!

On principle, though, it's best not to just leave payroll reports lying around, don't you agree. Yes, the top execs do have a lot out there in edgar filings, but ....

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!!

Are  you a consultant, working with and for several companies? 

I'm sure that a really good tracker could be built. Perfect? That might be a tall order.

I'm also pretty sure that such a tracker would have a major transactional database and only a few business tables involved.

• By "business table" I mean something like the little date based table I created...something that has inherent in it some "instructional" considerations for functions to be performed on the raw transactional data. Another business table might take all the awards, their dates of granting, dates of vesting, etc. That business table could drive how each exec acquires their additional shares as time rolls on.
• By "major database," I mean, in fact, a very large table that would--conceptually at least--combine all the information on all those executives and all the individual actions/transactions regarding their awards, dividends, cash in and cash out, into a single table.

One of the mistakes that many people make when first getting in to Excel is to break things apart, as you've done. It's in some ways easier to think of it that way, probably mirrors how we'd do it if we kept these records on paper. But the truth is that Excel can take a well-designed single database that tracks all the data on transactions and break it apart, dealing with individual rows based on such things as SSN and award number, calendar dates...Excel can take a single table and do wonders.

We actually make it harder for Excel to "show its stuff," as you are experiencing, when we break the raw data apart to make it easier for ourselves.

[#13]

@mathetes  Hi - no, i just do this for one company now.  Thanks for your attention on this!  I've just gotten the official reports that show the exact dividend amounts (that I was trying to calculate in advance) and they're not matching ... need to figure out why morgan stanley's numbers aren't the same as mine before I continue.  Appreciate your advice and sharing the info on the Xlookup.  i need to study that.