Defaulting a named range

[jymm]

Maybe I am just trying to make Excel do too much - I duh know.

Every time the user opens the workbook, I am loading a sheet from a MS SQL table (since it can change often). I then dynamically add a named range for the list from the SQL table. Several of the cells are set up to use these named ranges like pull downs.

Here is the problem. The main person who is using this workbook wants a default for the pull downs (ex he knows that Device #1 will run 'Formula 1' as one of it's "recipes" and Device #2 will run 'Formula 2' and 'Formula 3' (as well as potentially Formula 4-20).

If they select the formula from the 'drop down' the caluations which are based off of the value in the pull down & the information pulled from the table works 100% great. If I try to 'plug' a default value for each of these pull downs, the caluclations are not correct 'till you reselect it from the 'pull down'. The calculations all are similar to =VLOOKUP(B19, D34Formula,3)*C19 where B19 is the pull down.

Anyone have any VB experience in pre-selecting something like this?

 

[SkipVought]

Hi,

The way that you have stated your question does not require VBA code and if not, this should be posted in the Microsoft: Office Forum68.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[jymm]

Sorry for the confusion Skip - personally I do not care if it is in VBA or not - setting up the named ranges for the 'pick list' is done (in my case) in VBA
Sheets("Sunday").Names.Add Name:="D1Formula", RefersTo:="=offset(FormulasD1!$A$2,0,0,COUNTA(FormulasD1!$A:$A),13)", Visible:=False

finding last rows, populating from SQL using ADO, summarizing print info into a new sheet based on several other sheets of info... all in VBA - some other things in Excel formulas if need be.

If the setting of the default for the 'pick list' can be done elsewhere - great, I would prefer to do it in VBA, so that is why I posted here.

thanks for your consideration.

 

[SkipVought]

1. The Dynamic Named range that you posted does not need to run every time you refresh your table. Its ALREADY dynamic by virtue of COUNTA(FormulasD1!$A:$A). The 13 is NOT dynamic, but could be using COUNTA(FormulasD1!$1:$1) assuming that your querytable is anchored in A1.

2.

If I try to 'plug' a default value for each of these pull downs, the caluclations are not correct 'till you reselect it from the 'pull down'

How are you trying to 'plug' a default value? Please post an example.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[SkipVought]

The key is the control object, not the list range. What kind of dropdown object are you using?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}