Continuous Running If statment that returns entire row

[JasonRice]

Sorry but I am very new to excel programming. I need to know if the following is possible.

I have 4 different workbooks where each row has 4 columns that describes equipment, issues, possible solutions and if the item is open or closed.

I need to create a master document that lists all the open items on one continuous list.

 

[Fazza]

If the source files are closed, maybe with a query table?

From a fifth workbook set up a query via ALT-D-D-N and follow the wizard. If you don't have named ranges & see a message about no visible tables, select options then system tables to see the worksheets listed. I'm assuming the data is well structured with headers in row 1 & data immediately under. At the last step of the wizard, take the option to edit in MS Query. Edit the SQL to join the four data sources and apply the criteria. SQL might be like below. This is refreshable like a pivot table - ALT-D-R or via the external data toolbar or right click then refresh.

regards

SELECT *
FROM `file path`.table1
WHERE status = 'open'
UNION ALL
SELECT *
FROM `file path`.table2
WHERE status = 'open'
UNION ALL
SELECT *
FROM `file path`.table3
WHERE status = 'open'
UNION ALL
SELECT *
FROM `file path`.table4
WHERE status = 'open'

 

[JasonRice]

I have been working on this for most of the day and can not figure out how to union together the different files. I can reference them is seperately but then the rows do not update as more are added or taken away.

When I go through the wizard it allows me to select 1 file to open. Then I go to the SQL editor and try to add in the others but I get an error saying SQL entry can not be represented graphically and then could not add table.

This is what my code looks like:

SELECT `' RESL AHU-1 issues log$'`.Issue, `' RESL AHU-1 issues log$'`.`Issue Description`, `' RESL AHU-1 issues log$'`.`Date Identified`, `' RESL AHU-1 issues log$'`.Contr, `' RESL AHU-1 issues log$'`.`Action Taken`, `' RESL AHU-1 issues log$'`.`Issue Status_(Open / Closed)`, `' RESL AHU-1 issues log$'`.`Date Accept`
FROM `' RESL AHU-1 issues log$'` `' RESL AHU-1 issues log$'`
WHERE (`' RESL AHU-1 issues log$'`.`Issue Status_(Open / Closed)`='open')
UNION ALL
SELECT `List$`.Issue, `List$`.`Issue Description`, `List$`.`Date Identified`, `List$`.Contr, `List$`.`Action Taken`, `List$`.`Issue Status_(Open / Closed)`, `List$`.`Date Accept`
FROM `List$` `List$`
WHERE (`List$`.`Issue Status_(Open / Closed)`='open')

 

[Fazza]

Sounds like you're there.

Just acknowledge the message (about not being able to graphically represent etc): it shouldn't stop you doing what you want.

 

[JasonRice]

I just can't get it to find the second file for some reason.

 

[Fazza]

I think it is only that the file path needs to be in the SQL.

'change
FROM `List$` `List$`
 
'to
FROM `your full file path`.[List$][List$]

 

[JasonRice]

I still can't get it to find the file when I put it into the SQL editor. I put it in the format below and it returns that it can't find it. I am using 2007 so maybe it is not possible to join more than one file into a query. I can put multiple queries into the final sheet but this makes it so there are empty rows or overlapping rows.

 

[Fazza]

It is likely just a very minor detail, Jason, that causes it to not work successfully. Suggest you post the SQL that does work.

In case it makes a difference, set up the query from a separate workbook - so that it might give the exact syntax for external file references in the SQL.