Every month I need to compare the current list of this months projects
against the prior months projects to determine: a) any new projects added
b) any projects deleted c) $ variance from last month to this month for
existing projects.

The project data has separate $ breakdowns per month (ie Jan, Feb, Mar) and
not just a rolling total per year.

Currently I'm doing this via alot of worksheets to maintain vlookups and
calculations to be able to pull all the required data into one spreadsheet
for reporting.

This is very cumbersome because the formulas need to be updated each month
in order to point to the current months financials.

There has to be an easier way. I was thinking queries, but since I'm not
very familiar with them a) Not sure if they are the solution b) if so, woud
it be the same amount of work anyways?

Help please.

I'm not sure "query" is the correct concept here. Queries refer to
extracting data from databases and Excel is not a database.

In any event, setting an Excel workbook up for monthly reporting can be
cumbersome, but once you have the process down, it should be easier month
over month.

Post some of the formulas that you have to update monthly and explain in
more detail how your data is organized, and someone can give you specific
suggestions on how to make it more efficient.

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Every month I need to compare the current list of this months projects
against the prior months projects to determine: a) any new projects added
b) any projects deleted c) $ variance from last month to this month for
existing projects.

The project data has separate $ breakdowns per month (ie Jan, Feb, Mar) and
not just a rolling total per year.

Currently I'm doing this via alot of worksheets to maintain vlookups and
calculations to be able to pull all the required data into one spreadsheet
for reporting.

This is very cumbersome because the formulas need to be updated each month
in order to point to the current months financials.

There has to be an easier way. I was thinking queries, but since I'm not
very familiar with them a) Not sure if they are the solution b) if so, woud
it be the same amount of work anyways?

Help please.

Hi,

I'd heard people say they use a query workaround that enables them to use
Excel as a database. So I thought I would investigate this.

Personally, I'm ok with the excel process, but the hard part is handing it
over to adminstrators who are not comfortable with the technical aspect.

Thanks for your assistance. Mary-Lou.


Thanks for you assistance.

"Dave F" wrote:

I'm not sure "query" is the correct concept here. Queries refer to
extracting data from databases and Excel is not a database.

In any event, setting an Excel workbook up for monthly reporting can be
cumbersome, but once you have the process down, it should be easier month
over month.

Post some of the formulas that you have to update monthly and explain in
more detail how your data is organized, and someone can give you specific
suggestions on how to make it more efficient.

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Every month I need to compare the current list of this months projects
against the prior months projects to determine: a) any new projects added
b) any projects deleted c) $ variance from last month to this month for
existing projects.

The project data has separate $ breakdowns per month (ie Jan, Feb, Mar) and
not just a rolling total per year.

Currently I'm doing this via alot of worksheets to maintain vlookups and
calculations to be able to pull all the required data into one spreadsheet
for reporting.

This is very cumbersome because the formulas need to be updated each month
in order to point to the current months financials.

There has to be an easier way. I was thinking queries, but since I'm not
very familiar with them a) Not sure if they are the solution b) if so, woud
it be the same amount of work anyways?

Help please.

MLK
This is my second try to post this possible answer to your question. The
first time I posted it, the post dissappeared and I didn't make a copy, so I
am going to have to recreate it from scratch.
Basically, the "shape" of your data will determine what method that you
should use.
December January February
Proj1 12 22 closed
Proj2 15 closed
Proj3 22 56 99
Proj4 45 33 34
Proj5 85 58 67
Proj6 61 94 closed
Proj7 58 27 11
Proj8 17 closed
Proj9 11 45 86
Proj10 86 33 44


If each new month is added to the data tables as a new column, so that your
Header row contains the names of months across the top, then you could use
the Match function as explained in this article:

http://office.microsoft.com/en-us/ex...CL100570551033

Using the Match function to replace the 'column to return data from' term in
the VLOOKUP function allows you to put a single 'Report Month' input cell at
the top of your consolidation/reporting worksheets. You would then replace
the third term of all of the VLOOKUP formulas with a MATCH function that used
that Report Month cell as input for the term to Match and the data table
header row as the search range to find the match. The MATCH function returns
the position where it finds the match as a number, this number is used as the
return data column number by the VLOOKUP function.
You would only have to make one more change in all of your lookup formulas
to replace the column number of the currnent month's data with the MATCH
function.
After that, everytime you change the Report Month at the top, the whole
report automatically reflects that month's data.


On the other hand, if your data table is organized as a pure data table with
a date column, a project name column, a cost column, etc, then the filtering
characteristics of the sumif function might be used if you intended to stick
with pure functions. You could use a query, called a QueryTable in Excel.
Example Table:
Project Date Entry
Proj1 December 44
Proj2 December 83
Proj3 January 11
Proj1 January Closed
Proj3 January 88
Proj2 January 55
Proj2 February 67
Proj3 February 7

I named this table Project, then went to a new worksheet and created an
Data|Import External Data|New Database Query
I told the dialog box I wanted to use an Excel source and navigated to this
workbook.
I was then able to select the 'Project' table as the source. At this time I
got the 'choose fields to import' dialog. After selecting all fields I was
presented with an opportunity to filter the data. I chose "Contains" '1' for
the 'project' field. Next i was presented with the choice of sorting, i
selected the date column to sort by. The return is below:
Project Date Entry
Proj1 December 44
Proj1 January

Note that the "Closed" entry was not returned. In a data base each column
has to have only one type of data. Since that is how the Entry column started
off, the query assumed that the type was numerical and ignored the text.
So that is a simple example of how that would work.
Querys give you a lot of functionality that you would have to work pretty
hard to match with functions, but there is an overhead of initial rampup
learning, possibly data cleaning and table modifying as well as just naming
all of the tables. If you are really considering changing the way you do your
reports that much, consider moving all of the data to Access and using Excel
for reporting, consolidating and summing, charting, etc., instead of trying
to use Excel to emulate a database.
Remember that you would have to create a named table in Excel for each data
table you want the query to recognize, and the data tables need to be in a
database type of configuration, the header row is not for data (such as month
names) but for the column data name.

Hope this helps. Sorry my answer this morning dissappeared into the ether.
If this helps or if you need further help or clarification, please let us
know.

Note: did you know that you can select "Notify me of replies" and have an
email sent to you whenever someone posts an answer to this question? Not
everybody notices that.

SongBear


"MLK" wrote:

Every month I need to compare the current list of this months projects
against the prior months projects to determine: a) any new projects added
b) any projects deleted c) $ variance from last month to this month for
existing projects.

The project data has separate $ breakdowns per month (ie Jan, Feb, Mar) and
not just a rolling total per year.

Currently I'm doing this via alot of worksheets to maintain vlookups and
calculations to be able to pull all the required data into one spreadsheet
for reporting.

This is very cumbersome because the formulas need to be updated each month
in order to point to the current months financials.

There has to be an easier way. I was thinking queries, but since I'm not
very familiar with them a) Not sure if they are the solution b) if so, woud
it be the same amount of work anyways?

Help please.

WOW! Thanks for your assistance. I've lost my posts before too... big pain.

Anyways, I will look into your suggestions and see what I get.

Thanks again.

"SongBear" wrote:

MLK
This is my second try to post this possible answer to your question. The
first time I posted it, the post dissappeared and I didn't make a copy, so I
am going to have to recreate it from scratch.
Basically, the "shape" of your data will determine what method that you
should use.
December January February
Proj1 12 22 closed
Proj2 15 closed
Proj3 22 56 99
Proj4 45 33 34
Proj5 85 58 67
Proj6 61 94 closed
Proj7 58 27 11
Proj8 17 closed
Proj9 11 45 86
Proj10 86 33 44


If each new month is added to the data tables as a new column, so that your
Header row contains the names of months across the top, then you could use
the Match function as explained in this article:

http://office.microsoft.com/en-us/ex...CL100570551033

Using the Match function to replace the 'column to return data from' term in
the VLOOKUP function allows you to put a single 'Report Month' input cell at
the top of your consolidation/reporting worksheets. You would then replace
the third term of all of the VLOOKUP formulas with a MATCH function that used
that Report Month cell as input for the term to Match and the data table
header row as the search range to find the match. The MATCH function returns
the position where it finds the match as a number, this number is used as the
return data column number by the VLOOKUP function.
You would only have to make one more change in all of your lookup formulas
to replace the column number of the currnent month's data with the MATCH
function.
After that, everytime you change the Report Month at the top, the whole
report automatically reflects that month's data.


On the other hand, if your data table is organized as a pure data table with
a date column, a project name column, a cost column, etc, then the filtering
characteristics of the sumif function might be used if you intended to stick
with pure functions. You could use a query, called a QueryTable in Excel.
Example Table:
Project Date Entry
Proj1 December 44
Proj2 December 83
Proj3 January 11
Proj1 January Closed
Proj3 January 88
Proj2 January 55
Proj2 February 67
Proj3 February 7

I named this table Project, then went to a new worksheet and created an
Data|Import External Data|New Database Query
I told the dialog box I wanted to use an Excel source and navigated to this
workbook.
I was then able to select the 'Project' table as the source. At this time I
got the 'choose fields to import' dialog. After selecting all fields I was
presented with an opportunity to filter the data. I chose "Contains" '1' for
the 'project' field. Next i was presented with the choice of sorting, i
selected the date column to sort by. The return is below:
Project Date Entry
Proj1 December 44
Proj1 January

Note that the "Closed" entry was not returned. In a data base each column
has to have only one type of data. Since that is how the Entry column started
off, the query assumed that the type was numerical and ignored the text.
So that is a simple example of how that would work.
Querys give you a lot of functionality that you would have to work pretty
hard to match with functions, but there is an overhead of initial rampup
learning, possibly data cleaning and table modifying as well as just naming
all of the tables. If you are really considering changing the way you do your
reports that much, consider moving all of the data to Access and using Excel
for reporting, consolidating and summing, charting, etc., instead of trying
to use Excel to emulate a database.
Remember that you would have to create a named table in Excel for each data
table you want the query to recognize, and the data tables need to be in a
database type of configuration, the header row is not for data (such as month
names) but for the column data name.

Hope this helps. Sorry my answer this morning dissappeared into the ether.
If this helps or if you need further help or clarification, please let us
know.

Note: did you know that you can select "Notify me of replies" and have an
email sent to you whenever someone posts an answer to this question? Not
everybody notices that.

SongBear


"MLK" wrote:

Every month I need to compare the current list of this months projects
against the prior months projects to determine: a) any new projects added
b) any projects deleted c) $ variance from last month to this month for
existing projects.

The project data has separate $ breakdowns per month (ie Jan, Feb, Mar) and
not just a rolling total per year.

Currently I'm doing this via alot of worksheets to maintain vlookups and
calculations to be able to pull all the required data into one spreadsheet
for reporting.

This is very cumbersome because the formulas need to be updated each month
in order to point to the current months financials.

There has to be an easier way. I was thinking queries, but since I'm not
very familiar with them a) Not sure if they are the solution b) if so, woud
it be the same amount of work anyways?

Help please.

"MLK" wrote:

Every month I need to compare the current list of this months projects
against the prior months projects to determine: a) any new projects added
b) any projects deleted c) $ variance from last month to this month for
existing projects.

The project data has separate $ breakdowns per month (ie Jan, Feb, Mar) and
not just a rolling total per year.

Currently I'm doing this via alot of worksheets to maintain vlookups and
calculations to be able to pull all the required data into one spreadsheet
for reporting.

This is very cumbersome because the formulas need to be updated each month
in order to point to the current months financials.

There has to be an easier way. I was thinking queries, but since I'm not
very familiar with them a) Not sure if they are the solution b) if so, woud
it be the same amount of work anyways?

Help please.