Referring to a field of a database of other sheet

P

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
 
A

Ardus Petus

If I understand your question, the answer should be:

=Sheet1!A1

(adjust A1 range to your needs)

HTH
 
P

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
 
P

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
 
R

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.
 
P

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.
Click to expand...

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
 
R

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?
 
P

Paul Smith

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

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
 
Top