Referring to a field of a database of other sheet

[Paul Smith]

Dear All

How can one refer in sheet2 to the name of a field of a database placed
in sheet1?

Thanks in advance,

Paul

 

[Ardus Petus]

If I understand your question, the answer should be:

=Sheet1!A1

(adjust A1 range to your needs)

HTH

 

[Paul Smith]

OK, but then why does not the following work fine as an advanced
filter?

=Sheet1!A1>100

In Sheet1!A1 is thw field Wage.

=Wage>100

works fine if I place it inside a creiteria area in the sheet where the
database is placed.

Paul

 

[Paul Smith]

Sorry, the problem is not as I described. The problem is: if one uses
the criterium

=MONTH(Order date)=E$7

it does not work; however,

=MONTH(date)=E$7

and replacing the field name to "date", then it works. Aparently, the
cause of the problem is the existence of spaces in the name of the
field.

Paul

 

[Roger Govier]

Hi Paul

On sheet1 enter Wage in A1 and >100 in A2.
In Advanced Filter set your range of source data as Sheet2!range or
whatever, and set your criteria range as Sheet1!A1:A2.

Note. If you are trying to extract data to another Sheet (Sheet1), then
you need to start Advanced filter whilst Sheet1 is the current Sheet.
Don't worry about messages saying Excel cannot determine the source
range when you start from this sheet, continue and select Sheet2 range
as the source etc, and a location on hseet1 as the destination, and all
will work fine.

For more help and excellent examples, take a look at Debra Dalgleish's
site
http://www.contextures.com/xladvfilter01.html#ExtractWs

You will also find some sample downloads there.

 

[Paul Smith]

Sorry, the problem is not as I described. The problem is: if one uses

the criterium

=MONTH(Order date)=E$7

it does not work; however,

=MONTH(date)=E$7

and replacing the field name to "date", then it works. Aparently, the
cause of the problem is the existence of spaces in the name of the
field.

Thanks, Roger. I have found a solution that works if the criteria and
the database are in the same sheet:

Tools -> Options -> Calculation -> Accept labels in formulas.

However, the problem persists if I have the criteria and the database
in different sheets. Can one overcome this remaining problem?

Paul

 

[Roger Govier]

Hi Paul

Have you tried starting the Advanced Filter from the sheet with the
criteria, rather than from the sheet with the database?

 

[Paul Smith]

Have you tried starting the Advanced Filter from the sheet with the

criteria, rather than from the sheet with the database?

Thanks, Roger. I had tried that already. Furthermore, the same problem
occurs if you place the criteria and the following formula in the same
sheet (sheet2) and the database in a different one (sheet 1):

Criteria
=MONTH(Order date)=3

Formula
=DSUM(sheet1!A1:C20;3;criteria)

Surprisingly, if you use "Date" instead of "Order date" as the field
name, there is no problem. The reported occurs even with "Tools ->
Options -> Calculation -> Accept labels in formulas".

I suspect that it is a bug of Excel.

Paul