Microsoft Query Cell Reference for a field name

[johnvog]

I am attempting to use Microsoft Query on an excel spreadsheet that has columns for Account, Jan, Feb, Mar, etc. In the months columns is an amount. I have 2 questions.
1. Can I have a cell reference on my results page that I can use to refer to the columns with the month names.
2. I have a column that refers to a cell inside the table that is created (like B2) that displays the number as positive or negative depending on how I need it to (debit or credit). Is there a way that I can have that formula remain the same when I refresh the query and the data changes. (It gives me #ref now.)

My Query is below:

SELECT TRIM(`'Balance Sheet$'`.Account) AS 'Account', `'Balance Sheet$'`.`Jan`
FROM `'Balance Sheet$'` `'Balance Sheet$'`
WHERE (`'Balance Sheet$'`.Account Not Like '%Total %' And `'Balance Sheet$'`.Account Not Like '%Net %') AND (`'Balance Sheet$'`.`Jan`<>0)

My goals are:
A. To be able to change a cell value from say Jan to Feb so that I can refresh the query and retrieve the new values.
B. To be able to have my formula that refers to a cell inside the table created so that I do not have to recreate the formula every time I refresh the query with new data.
C. Copy and paste this data into a new table for creating the reports I need.

Thank you in advance for any help.

John Vogrin

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use a parameter query. Here's a tutorial:

Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

 

[johnvog]

Thank you for the quick response. I am looking at the link that you suggested; however, I am not sure this is going to do what I need. I apologize if I was unclear.

Is it possible for me to have a cell in an excel spreadsheet that would allow me to change the field in the SELECT and WHERE clauses.

Example (without MS Query formatting)

ORIGINAL
SELECT TRIM(Account) as Account, Jan
FROM Excel Sheet
WHERE Account NOT LIKE '%Total %' AND Account NOT LIKE '%Net %' AND Jan <> 0

CHANGE
SELECT TRIM(Account) as Account, Feb
FROM Excel Sheet
WHERE Account NOT LIKE '%Total %' AND Account NOT LIKE '%Net %' AND Feb <> 0

Thank you again.

John

 

[Andrew Poulsom]

Yes you can have a cell as a parameter. Here's another example:

https://www.youtube.com/watch?v=P9cUYpXIKsU

 

[johnvog]

I am beginning to think that what I am trying to do is not possible, but I will give it one more try.

Assume the value I need to use is located in Cell G1 (and the value is Jan) can I do something like this in the query:

Select Trim(Account) As Account, [G1]
From ExcelSheet
Where Account NOT Like '%Total %' AND [G1] <> 0

So, I need the parameter to set the column (field) I am wanting to use not the value I am searching for.

 

[Andrew Poulsom]

To have a dynamic Field I think you would need to build the SQL in VBA.

 

[johnvog]

Ok, I was coming to the same conclusion. Thank you for your help.