indirect function in different worksheets, losing my hair!
 

Dear all,

please help me on the following.

DBMAX(A1:INDIRECT("B"&E4),B1,D1:D2)
I have this formula working in a single worksheet. The formula returns the
maximum value in the B colum of a database with colums A-B, with the number
of rows, defined by a value in in E4. Criteria for the search are set in D1
and D2. THIS WORKS,

However:

THIS DOESN'T WORK,

DBMAX('test tab'!A1:INDIRECT("B"&'test tab'!E4),'test tab'!B1,'test
tab'!D1:D2)

Basicallly I want the DBMAX calculation to happen in another work sheet. The
database, the row definer (E4), and the criteria are all the first worksheet
'test tab'.

Why does it not work?????????

more importantly, how can I make it work!!!

thanks

--
Alexander Banz

Add one more reference to the sheet name:

=DMAX('test tab'!A1:INDIRECT("'test tab'!B"&'test tab'!E4),'test
tab'!B1,'test tab'!D1:D2)

Alexander Banz wrote:
Dear all,

please help me on the following.

DBMAX(A1:INDIRECT("B"&E4),B1,D1:D2)
I have this formula working in a single worksheet. The formula returns the
maximum value in the B colum of a database with colums A-B, with the number
of rows, defined by a value in in E4. Criteria for the search are set in D1
and D2. THIS WORKS,

However:

THIS DOESN'T WORK,

DBMAX('test tab'!A1:INDIRECT("B"&'test tab'!E4),'test tab'!B1,'test
tab'!D1:D2)

Basicallly I want the DBMAX calculation to happen in another work sheet. The
database, the row definer (E4), and the criteria are all the first worksheet
'test tab'.

Why does it not work?????????

more importantly, how can I make it work!!!

thanks



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks debra,

That was very helpful, sorry for the late thank you!

Alex

"Debra Dalgleish" wrote:

Add one more reference to the sheet name:

=DMAX('test tab'!A1:INDIRECT("'test tab'!B"&'test tab'!E4),'test
tab'!B1,'test tab'!D1:D2)

Alexander Banz wrote:
Dear all,

please help me on the following.

DBMAX(A1:INDIRECT("B"&E4),B1,D1:D2)
I have this formula working in a single worksheet. The formula returns the
maximum value in the B colum of a database with colums A-B, with the number
of rows, defined by a value in in E4. Criteria for the search are set in D1
and D2. THIS WORKS,

However:

THIS DOESN'T WORK,

DBMAX('test tab'!A1:INDIRECT("B"&'test tab'!E4),'test tab'!B1,'test
tab'!D1:D2)

Basicallly I want the DBMAX calculation to happen in another work sheet. The
database, the row definer (E4), and the criteria are all the first worksheet
'test tab'.

Why does it not work?????????

more importantly, how can I make it work!!!

thanks



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html