Excel data validation: Allowing list

[fiel]

I have an excel workbook that will perform calculations for wire sizes. Based on a certain "amp load", I need to be able to display multiple combinations of wires used. I have created a seperate worksheet listing different wire combinations for various "amp loads".

Is there a way to have a user defined function return the source of the list needed?

I have it set in a way so that

If AmpLoad <= 100 then
' return list source for this load

ElseIf AmpLoad > 100 and AmpLoad <= 200 then
' return list source for this load

End If

I just can't figure out the statement inside to return a list source...

 

[SkipVought]

Hi,

I'd use MS Query on a table of amp loads and wire specs, where the criteris is based on the SelectedLoad.

Use a separate sheet for the source table.

The QueryTable resultset of Wire Specs would be a parameter query base on the SelectedLoad.

You could use a Data > Validation cell to either pick from a list of loads or just have the user enter a load. Whan that cell changes value the query fires and returns the wire Specs.

faq68-5829

FYI, it can be done with no VBA, or not -- pick yer poison.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[fiel]

I have my resultset tables set up already. Is there a way I could just change the list source directly through a macro without using a query or would the query be required?

 

[SkipVought]

"I have my resultset tables set up already"
"...without using a query ..."

resultsets are from Queries. ???

You could AutoFilter the source table using the SelectedLoad as a criteria.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[fiel]

If I use MS Query, will I have to worry about keeping my tables/columns within my workbook in a permanent location? Because this file would be shared in a network drive and passed on to other computers through email.

 

[Fr33dan]

If shouldn't be a problem if you give the cells names(Insert>Names>Define). Excel will then keep track of their location if they're moved

 

[SkipVought]

The issue is NOT the name of ranges, although I strongly recommend using named ranges.

The issue is the query Connect String, which could change when a user does a SaveAs.

To hand this, turn on your macro recorder and record EDITING THE QUERY, returning data to Excel from the QBE Editor.

Post back with your recorded code to see how it can be modified to accomodate ANYTHING the user might do. (no big deal!)

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[fiel]

I don't suppose there's a way to just use VBA to do Data > Validation, and change the source of my list by simply changing the reference name is there? I'm currently trying to put together this MS Query but don't seem to be getting much luck. Err.

 

[SkipVought]

Turn on you macro recorder and change you Data > Validation - List reference.

BUT, it is NOT a best and acceptable practice to name SIMILAR data with different names or put similar data in separate list or tables.

You can use the OFFSET function on your sheet in conjunction with MATCH and COUNTIF, to reference parts of a range.

You could use VBA, but why, in this case? I still prefer the Query approch, but OFFSET will also do the trick, with a bit more effort.



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]