Cell Reference in Excel 2010

[shelby55]

Hello

I have a worksheet that uses MS Query to get at the data from a worksheet in the same workbook.

I want to reference one of the columns from the query and compare it to:

=IF(AND(MONTH([@[''Date Assessment Completed'']])=MONTH(Table_ReferAssess_1[[#Headers],[4/01/12]]), YEAR([@[''Date Assessment Completed'']])=YEAR(Table_ReferAssess_1[[#Headers],[4/01/12]])),DATEDIF([@[''Referral Received Date'']],[@[''Date Assessment Completed'']],"d"),0)

But these absolute references are causing issues when someone else opens the worksheet (they get an ODBC error message indicating an invalid path name).

This wasn't an issue in the 2003 version....is there something I can do to work around? Thanks.

 

[SkipVought]

Don't you have the sPath & "\" & sDB variables in ALL your In-Workbook queries? I would assume that the Workbook_Open event points the query connection(s) correctly right up front.

So does the user ENABLE MACROS and still get this error?

Don't believe that the formula has anything to do with an error UNLESS the user is running a version prior to 2007, that cannot use Structured Table Reference
feature

Skip,

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

 

[SkipVought]

However, there probably is a problem in your formula, as you are comparing a DATE, it seems, to a HEADING in your table.
[tt]
MONTH([@[''Date Assessment Completed'']])
[/tt]
what is the VALUE of [@[''Date Assessment Completed'']] (change the format of this cell to GENERAL to see the VALUE)

The HEADING value Table_ReferAssess_1[[#Headers],[4/01/12]] is the TEXT value 4/01/12. It is NOT a Date Value!!!

In order for that equality to work, assuming that [@[''Date Assessment Completed'']] is a Date Value, you must convert the text to date...
[tt]
MONTH([@[''Date Assessment Completed'']])=MONTH(DateValue(Table_ReferAssess_1[[#Headers],[4/01/12]]))
[/tt]
and the same for Year of the heading value.

I fear that this heading reference is going to bite you in succeeding months. I'd advise looking for some other way to do this, maybe using an ABSOLUTE CELL REFERENCE rather than a structured table reference.

Skip,

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

 

[shelby55]

Hi Skip

Sorry I didn't answer sooner...I was away from my office. Yes I have the code from the other post and the formula and everything works great on my computer (home office, not on a network). When I sent it to someone else, it wouldn't work for them and they got the message they did. I initially thought it was an ODBC driver problem but the IT guy from the hospital where this was sent said it was an issue with my workbook.

This is a version of a workbook you helped me with awhile ago. I have the query pulling in two dates (referral and assessment) and then I have a column per month. The header for the month is actually the first date of the month so April is 4/1/2012 etc.

Before where I needed to split the date difference between the two dates into the months they occurred in i.e. if referral was April 1 and Assessment was May 15 then 29 days would go in April and 15 in May. NOW I just need to include all the days in the month the assessment occurred in. So for above, now all 44 days would go in April.

The formula therefore is comparing the assessment date to the header and if it matches by month and year, then datedif it.

But it seems that the way the formula is automatically written when using Excel 10 it references the workbook itself thus the error....unless this could still be an ODBC issue at their end?

 

[SkipVought]

Did you read my last post regarding DateValue?

Skip,

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