Matrix Type Report With Input Box [Amendment]

TickerReport.xlsHi,

I would like some advice on how I could get something like this automated. First I must say I’m pretty novice with Excel VBA…

Basically this is a report where ideally someone would get a popup box asking them to input a TICKER. The macro would then run and find the following associated with that ticker and give you output. Here are the fields that should be associated with that ticker.

Account
Cusip
% Market Value

The way I envision this working would be for one page to have all our holdings (I can create this). Then whatever the user inputs into the box will take that relevant information and format it to a different sheet. The sheet would have so Accounts down the side of it, then along the top would be all the Cusip’s associated with that Ticker. In the columns under each of those tickers would be the % of Market Value for that account, and the final column would contain Account Market Value (which could just be a VLOOKUP to the other sheet).

If anyone can help me out that would be great. We have about 200 accounts though, and as you can see different Tickers will have a different number of cusips associated with it, so the number of columns will change on a daily basis. Also the number of Accounts will also change, so I might add an account one day, or 3, so the formulas need to be able to keep up.

On the spreadsheet, the first tab is what I can get daily, I guess a macro or button would be placed on it to activate the text box popup, then the macro to generate the following sheet will run too.


******** Updated...

Sorry for the quick amendment…

I would also like to add a column in between each cusip column that just returns the Par Value number for that cusip and account. Basically this will make it one step easier for us to calculate the par value needed to obtain a certain % of Market Value in that account. thanks again!