Make a list of values using Index from streaming data that lists duplicates with differing resulting values

sshelt15

New Member
Joined
Aug 27, 2014
Messages
10
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]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top