Search results for query: *

The value in E1 is S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012 Forecast 1+11.xlsx] After hitting F9 on formula =VLOOKUP(B8,INDIRECT("'" & E1 & "Cons by month'!$B$8:$O$199"),$K$4,0), I see: =VLOOKUP(B8,INDIRECT("'S:\Accounting\Financials\Linked Spreadsheets\[Consolidated 2012...

I've tried: =VLOOKUP(B8,"'" & INDIRECT(E1 & "Cons by month'!$B$8:$O$199"),$K$4,0) and =VLOOKUP(B8,INDIRECT("'" & E1 & "Cons by month'!$B$8:$O$199"),$K$4,0) both result in a #REF error and this is while that workbook that the VLOOKUP is referencing is opened. E1 is only going to contain the...

Thanks, Skip The use of =VLOOKUP(B8,"'" & INDIRECT(E1) & "Cons by month'!$B$8:$O$199",$K$4,0) in the cell results in a #REF! error even with the file open. However, the user will not have the other workbook open when they use this function. The hardcoded path formula...

So, I've created a named combo box control in a MS Excel 2007 workbook containing file names that are stored on another worksheet (DriveFiles) in the same workbook. I've set the combo box cell link to D1 on my worksheet and set E1 =IF(D1="","","S:\Accounting\Financials\Linked Spreadsheets\[" &...

The user doesn't want to have to do anything other than run a macro and the (9) worksheets be populated.

BTW, the users are on MS Excel 2003 for an indeterminate amount of time... @Combo, not sure if that will work for the scenario...but if it seems like it might, then I might need some clarification as to how this can be accomplished @Skip, All worksheets contain data from the same source but...

The users have a worksheet in the workbook that contains thousands of rows of data. Based on particular criteria, if the row of data meets that crtieria they want it "moved" to a sheet containing all the rows meeting that criteria so they can do some further manipulations. They have 9...

@Combo - you're probably right, I wouldn't be able to trap that warning. Maybe I moreso would rather just catch whenever the user chooses a delete action and then only delete the selection and not the queries... @Skip - Your "hide the query sheet and reference it for the users to play with"...

How can one trap the event when the user attempts to delete a range and thereby the underlying query on a worksheet? Currently, the default warning message (The range you deleted is associated with a query that retrieves data from an external source. Do you want to delete the query in addition...

I'm actually going to keep the whole adding a query table sub just in case the user inadvertently deletes the query table from the worksheet (I'm proof that this can happen because I accidently deleted the table when trying to delete that total row). The subscript out of range error in the...

It was the blasted InitWorsheets routine that was being used to clear up the worksheet before putting the new query table on it...it was deleting the query table from the sheet prior to the macro run with the ChangeDataConnection sub in it (remember it had to be run once with the original sub so...

I ran through the original sub in order to establish the connections, then ran the macro with the sub you sent (the sub to remove the queries was commented out so the connections remained). The connections are named generically Connection, Connection1, Connection2, etc.

I get the "Error in ChangeDataConnection: 9 Subscript out of range" error when trying to run the following code. The sub errors when it hits the With Sheets(sWS).QueryTables(1) line: Private Sub ChangeDataConnection(sSQL As String, sWS As String) On Error GoTo Error_ChangeDataConnection...

Below is the code used to add the Query Table...there might be some unused references and a couple of mis-placed comments...it's not been cleaned up completely.. Private Sub SelectDATA(sSQL As String, sWS As String) On Error GoTo Error_SelectDATA ' Create a connection for each iteration...

The subroutine order is like delete any connections from previous runs, clear worksheets ranges A8 through K(last row), create query tables, and then run the totaling sub. The code below is what is used to delete the connections. My thought was that the query defs are stored on the user's...

Ummmmm....shrug...that's what THEY want, not me...I'd prefer not to look for the total either. :-) But, that being said, I will say that the data on these tabs is coming from a Query Table soooooo are you saying that I should maybe not try to put the total at the end of the query table? or...

Thanks, Geoff and Skip...here's what I changed the code to. Not that it changed my results any...when I put a break in the code and step through, this code works perfectly. When I run the macro without any breaks, the top 8 columns of the worksheets are shifted to the right by two, leaving the...

Thanks for pointing me to the right forum and your reply anyhow. Would you by chance have a code sample of indexing a row pointer and using the range property or cells property? I'm pretty much a novice at vba for excel. That was all code to add a Total label and value at the end of whatever...

When I step through the code below, the results on the worksheet are as they should be (the total label is in J whatever and the total is in K whatever) but when I run through the macro without any code breaks, the worksheet header rows (in this case range A1:K7) are shifted over to range C1:M7...

@Skip I'm unsure how to run actual complex queries against a selected range. Do you have an example? @UnsolvedCoding I would like to select all the rows and columns of a particular spreadsheet to include headers (if needed to identify column names), put all data into a temporary dataset, run a...