Excel (2010) Data Validation 1

[PWD]

Good afternoon. Is there a lazy an easy way to produce a list for data validation from another list that contains many instances (repeats) of words? A colleague has a list of customers and their spend per invoice and wanted to know if there was a quick way of producing a drop-down list from their names and have another cell with the results based on that cell - the last part I can do. Basically I'm treating this as a "What if" exercise rather than trying to work out why he's doing what he's doing etc. I know that he could filter but he just asked me what would be possible.

Many thanks,
D€$

 

[SkipVought]

hi,

Make a unique list using the source data:

1) PivotTable
2) Advanced Filter
3) MS Query

Either of these methods can produce a unique list. I perfer the latter. faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I'd read previous posts of yours where you favour MS Query so I thought I'd give it a go. It is linked to the data but I've probably done it incorrectly. What I was thinking of was a way of obtaining an unique list of names from all the names and then use that unique list in Data Validation. It's no big deal, my colleague just asked if I knew if it were possible to be able to sum the invoice values of any given customer just by selecting one value in one cell so that it's really simple once it's been set up. Which then made me wonder how I would go about it.

Many thanks,
D€$

 

[SkipVought]

It is linked to the data but I've probably done it incorrectly

What did you do?

What results did you get?

Please be very specific with your description.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I followed the MS Query steps and got what appears to be a linked table - useful in other circumstances - but with all the rows with all the customer names. So I decided on contestant #1 and created a Pivot Table - based on the whole of the customer name columnn, which then, unfortunately, contains (blanks), removed its Header row and Grand Total row then created a 'target' cell that is Data Validated to the whole of that Pivot Table's column. This appears to offer the flexibilty of being able to add more customer lines with the provision of their total spend using a simple SUMIF referencing the 'target' cell.

Many thanks,
D€$

 

[SkipVought]

linked table ...with all the rows with all the customer names

THAT is exactly what you want!!!

Select DISTINCT [customer name]
from [Sheet1$]

assuming that your source table is on Sheet1 AND customer name is in ROW 1, will result in a list of unique customer names.

Use that list (Dynamic Named Range Name) as the source for your Data >Validation -- LIST


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Bit of a tweak here, bit of a tweak there - SORTED. Thanx. I'll have to try to remember all about MS Query, Dynamic Named Ranges & Using Named Ranges in data Validation that I've put together for this

Just one more thing; is it possible to edit the created table once it exists? I encountered an issue when I renamed the sheet from "Sheet1" to something 'nicer' and, of course, it wouldn't refresh.

Many thanks,
D€$

 

[SkipVought]

Select IN the querytable and...

1) Either edit the query and start all over again by pointing to the proper sheet

or

2) Design > External Table Data > Data Range Properties > Connection Properties > Definition TAB > Command Text textbox.

This textbox contains your query SQL. CHANGE the table name in the FROM clause only like...
[tt]
WAS SQL
SELECT `Data1$`.dy, `Data1$`.mo, `Data1$`.yr, `Data1$`.title, `Data1$`.content
FROM `C:\Documents and Settings\ii36250\My Documents\Book1.xlsx`.`[highlight]Data1$[/highlight]` `Data1$`

NEW SQL[/b]
SELECT `Data1$`.dy, `Data1$`.mo, `Data1$`.yr, `Data1$`.title, `Data1$`.content
FROM `C:\Documents and Settings\ii36250\My Documents\Book1.xlsx`.`[highlight]Data$[/highlight]` `Data1$`
[/tt]
to change the sheet name from [highlight]Data1[/highlight] to [highlight]Data[/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Found out that I can see what's what by clicking "Connections" but this reveals that the MS Query table is liked to this workbook but has the full path, so when I send it to my colleague it doesn't (can't) refresh.

Many thanks,
D€$

 

[SkipVought]

What is the path?

Is the workbook on YOUR hard drive? TILT!!!

If its on a common server, then define the connection using the logical server name and NOT a mapped drive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

duh! Sorry.

You will have to code your query and in your code CHANGE the ConnectionString on the fly. Here's an example...

Sub CC_List()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path  '[b]path is wherever the user saves the workbook[/b]
    
    sDB = ThisWorkbook.Name  '[b]name may change too[/b]
'[b]1046 is for a 2007 version    [/b]
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"""
'[b]this would be YOUR sql code[/b]
    sSQL = sSQL & "SELECT DISTINCT mbs.SF_CC"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `MBS$` mbs"

    With wsCC_List.ListObjects("tCC_List").QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
    End With
End Sub

Here's how to generate code for YOUR querytable...

1) in the VB Editor start a procedure

sub myquery()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path  '[b]path is wherever the user saves the workbook[/b]
    
    sDB = ThisWorkbook.Name  '[b]name may change too[/b]

    sConn = ""

    With sheets("YOUR SHEET NAME HERE").ListObjects("tCC_List").QueryTable
        debug.print .Connection
        debug.print .CommandText
    End With
end sub

RUN this sub.

View the IMMEDIATE WINDOW ctr+G

COPY the first line (starts with ODBC)

PASTE it between the QUOTES in sConn = ""

COPY the SQL (Start with Select)

Modify the Connection variable Conn string & CommandText variable sSQL string as modeled above and CHANGE the code in the With...End structure as modeled above, assigning sConn and sSQL and Refreshing the query.

This procedure will have to be executed appropriately, either via a button or some workbook event, like Workbook Open.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Dang. I appear to get to the same place with Data | Connections | Double-click on "Query from Excel Files" and selecting the 'definition' tab. At the moment I have

 SELECT DISTINCT `Sheet1$`.Customer
FROM `Sheet1$` `Sheet1$`

But if I just change the first Sheet1 to "Kev" (or all of them) I get

[Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

It seems a shame that you can't have a "ThisWorkbook" in the Connection string.
This would seem to make it impossible to create these sort of workbooks for other people - especially those not even on my network.

Many thanks,
D€$

 

[SkipVought]

please post EXACTLY what your SQL string WAS before and IS now when the error occurred.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip it was:-

SELECT DISTINCT `Sheet1$`.Customer
FROM `Sheet1$` `Sheet1$`

And I changed it to either:-

SELECT DISTINCT `Sheet1$`.Customer
FROM `Kev$` `Sheet1$`

or

SELECT DISTINCT `Kev$`.Customer
FROM `Kev$` `Kev$`

but both gave that error.

TBH I think this has taken up enough of your valuable time as I'm not going to be able to use MSQuery to give my colleague what he really wants - and that's something that's really simple for him to use. Thanks anyway as, like I said, I have learnt a whole bunch of neat stuff out of this.

Many thanks,
D€$

 

[SkipVought]

In 2007+ it seems that you must SAVE your workbook prior to executing the query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Well I'll be darned!! No error after saving workbook. Thanx.

Still think it's a shame your can't just connect to this workbook.

Many thanks,
D€$

 

[SkipVought]

No extra charge! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}