Good Evening!
I am working on a spreadsheet that links data from Thomson ONE. I've gotten pretty far on my own, but I am struggling with the last piece. For reference, I'm making a portfolio statement that can be easily changed if we end up buying or selling an investment.
Trying to create a list of expiration dates that correlate with a selected strike price in a cell drop down list. It also needs to exclude blanks so that the drop down menu doesn't have large gaps between selections. The problem that I'm running into is that there are several duplicate strike prices and each has a different expiration date.
Here is the relevant data placement:
[TABLE="width: 1109"]
<tbody>[TR]
[TD="class: xl67, width: 412"]B14- 'Strike Price' column Header : data can extend to 1000
C14-'Expiration date' column header:data can extend to 1000
Lookup Value / Strike Price Value: 'EquityList'!$G$32
I tried the following formula on my spreadsheet in cell L15. I dragged the formula down to the end of the page. It only lists 2/4 dates that should be on the list. I'm not sure what's wrong with my formula and was hoping to find the answer.
{=IF(ISERROR(INDEX($B$14:$C$1000,SMALL(IF($B$14:$B$1000='Equity List'!$G$32,ROW($B$14:$B$1000)),ROW(1:1)),2)),"",INDEX($B$14:$C$1000,SMALL(IF($B$14:$B$1000='Equity List'!$G$32,ROW($B$14:$B$1000)),ROW(1:1)),2))}
Also, if anyone knows an easy way to make the font larger in custom drop down menu, that would be helpful as well. Is VBA the only way?
Thank you in advance to anyone that can help!!
Sincerely,
Sally [/TD]
[TD="class: xl68, width: 71"]
[/TD]
[TD="class: xl69, width: 126"][/TD]
[TD="class: xl69, width: 52"][/TD]
[TD="class: xl69, width: 84"][/TD]
[TD="class: xl69, width: 110"][/TD]
[TD="class: xl69, width: 85"][/TD]
[TD="class: xl69, width: 75"][/TD]
[TD="class: xl70, width: 94"][/TD]
[/TR]
</tbody>[/TABLE]
I am working on a spreadsheet that links data from Thomson ONE. I've gotten pretty far on my own, but I am struggling with the last piece. For reference, I'm making a portfolio statement that can be easily changed if we end up buying or selling an investment.
Trying to create a list of expiration dates that correlate with a selected strike price in a cell drop down list. It also needs to exclude blanks so that the drop down menu doesn't have large gaps between selections. The problem that I'm running into is that there are several duplicate strike prices and each has a different expiration date.
Here is the relevant data placement:
[TABLE="width: 1109"]
<tbody>[TR]
[TD="class: xl67, width: 412"]B14- 'Strike Price' column Header : data can extend to 1000
C14-'Expiration date' column header:data can extend to 1000
Lookup Value / Strike Price Value: 'EquityList'!$G$32
I tried the following formula on my spreadsheet in cell L15. I dragged the formula down to the end of the page. It only lists 2/4 dates that should be on the list. I'm not sure what's wrong with my formula and was hoping to find the answer.
{=IF(ISERROR(INDEX($B$14:$C$1000,SMALL(IF($B$14:$B$1000='Equity List'!$G$32,ROW($B$14:$B$1000)),ROW(1:1)),2)),"",INDEX($B$14:$C$1000,SMALL(IF($B$14:$B$1000='Equity List'!$G$32,ROW($B$14:$B$1000)),ROW(1:1)),2))}
Also, if anyone knows an easy way to make the font larger in custom drop down menu, that would be helpful as well. Is VBA the only way?
Thank you in advance to anyone that can help!!
Sincerely,
Sally [/TD]
[TD="class: xl68, width: 71"]
[/TD]
[TD="class: xl69, width: 126"][/TD]
[TD="class: xl69, width: 52"][/TD]
[TD="class: xl69, width: 84"][/TD]
[TD="class: xl69, width: 110"][/TD]
[TD="class: xl69, width: 85"][/TD]
[TD="class: xl69, width: 75"][/TD]
[TD="class: xl70, width: 94"][/TD]
[/TR]
</tbody>[/TABLE]