Extracting a substring from an array, eliminating duplicates

[Sevigny86]

Hi everyone,

I'm trying to set something up here and I'm a little lost in my functions, hopefully someone can help out!

I have a dynamic array of a portfolio which has a series of symbols ex. OMCL-US (because it pulls data from a database that requires the "-US" portion). But now, I'm trying to dynamically refer to a worksheet which will hold specific data for that stock. I understand the Indirect function does not allow for symbols ex. "-" so I need to name my sheets ex. OMCL and not OMCL-US.

Consequently, when I create my table, pulling my unique stock list from my portfolio it comes out as ex. "OMCL-US" as thats how it is found in the data base. I would like the output to rather be "OMCL" instead of "OMCL-US" so that my indirect link can function properly.

Currently this is my formula to pull my data from the portfolio page:

=IFERROR(INDEX('Portfolio Weightings'!$B$10:$B$64,MATCH(0,INDEX(COUNTIF($A$2:A2,'Portfolio Weightings'!$B$10:$B$64),0,0),0)),0)

To pull my desired data I then refer to the output cell with:

=IF(A3=0,"",IFERROR(INDIRECT(B3&"!$b$10"),"N/A")

I know I can pull the required text before the "-" using:

=IF(A3=0,"",LEFT(A3, MIN(FIND("-", A3&"-"))-1))

But I would like to combine them so it outputs correctly right away, rather than use a second column to convert.

Thanks in advance!

 

[Rick Rothstein]

=IF(A3=0,"",IFERROR(INDIRECT(B3&"!$b$10"),"N/A")

INDIRECT can refer to a sheet with a dash in its name, you simply have to treat it the same way you would if there was an embedded space... surround the name with apostrophes...

=IF(A3=0,"",IFERROR(INDIRECT("'"&B3&"'!B10"),"N/A"))

 

[Sevigny86]

Ha, I was misinformed.

Thank you so much! So much simpler than the rabbit hole I was digging down