Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAXs criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the €œcurrent row€�).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=€�MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]€�

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cells
content is =ExamTable[ID], this cells value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank

Instead of DMAX, use an array entered MAX IF formula. For example, in
cell G3:

=MAX(IF(Mesh[Title ID]=ExamTable[[#This Row],[Item Title]],Mesh[Date]))

After you type the formula in the cell, press Ctrl+Shift+Enter, to array
enter the formula. Curly brackets will appear around the formula.


Gus wrote:
Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAXs criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the €œcurrent row€�).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=€�MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]€�

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cells
content is =ExamTable[ID], this cells value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thank you very much, Debra, it works fine and as expected!

Gus

"Debra Dalgleish" wrote:

Instead of DMAX, use an array entered MAX IF formula. For example, in
cell G3:

=MAX(IF(Mesh[Title ID]=ExamTable[[#This Row],[Item Title]],Mesh[Date]))

After you type the formula in the cell, press Ctrl+Shift+Enter, to array
enter the formula. Curly brackets will appear around the formula.


Gus wrote:
Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAXs criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the €œcurrent row€�).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=€�MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]€�

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cells
content is =ExamTable[ID], this cells value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that it worked.

Gus wrote:
Thank you very much, Debra, it works fine and as expected!

Gus

"Debra Dalgleish" wrote:


Instead of DMAX, use an array entered MAX IF formula. For example, in
cell G3:

=MAX(IF(Mesh[Title ID]=ExamTable[[#This Row],[Item Title]],Mesh[Date]))

After you type the formula in the cell, press Ctrl+Shift+Enter, to array
enter the formula. Curly brackets will appear around the formula.


Gus wrote:

Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAXs criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the €œcurrent row€�).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=€�MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]€�

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cells
content is =ExamTable[ID], this cells value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html