Forum Discussion
Help with formula
Hello, I am trying to remove the blank selections from a dependent drop list I have created in Excel and I am having a hard time figuring out what the formula would be to get this accomplished. A li...
Hello H1D3F,
if the different managers have varying numbers of portfolios, then you need a distinct range for each manager that includes only the rows of data for that manager. In the attached spreadsheet you find three managers with three different range names. They have been created by selecting the manager name and the cells with text below it, then using Insert > Name > Create with the default to use the text in the top row for the range name. Any space in the range name will be replaced with an underscore.
The range name for portfolio uses the formula
=INDIRECT(SUBSTITUTE(Sheet1!$A3," ","_"))
It replaces the space in column A with an underscore and then uses that string to refer to the range name.