|
|
I have stored some data in sheet named "Data" in database format with
following headers:
Column Heading Type of data
===== ====== ========
A Supplier_Name Text
B Machine_Model Text
C Table_Length Numeric
D Table_Width Numeric
E Spindle_Diameter Numeric
Some of the data under columns C, D & E are blank. Columns. A:D is named as
"Data"
The criterion for quering the database is setup as follows in another sheet
named "Query":
Col. A
Col.B Col.C Col.D
Col.E
Row1 Supplier_Name Machine_Model
Table_Length Table_Width Spindle_Diameter
Row2 =IF(ISBLANK(A3),"",A3) =IF(ISBLANK(B3),"",B3)
<----------------Problem here-------------------------->
Row3
The range A1:E2 is defined as Query_Critrerion. Row 2 is hidden and user
input is picked up in Row 3; the formulas under Row 2 convert the user input
for defining the criteria.
Then I use the formula =DCOUNTA(Data,1,Qry_Criterion) in a cell in the
"Query" sheet to get the count of records matching the criteria entered.
The moment user types in his creiteira, immediaely, the cell displays the
record count.
The user should be allowed to input expressions like >5, >=10, etc. under
C3, D3 & D3 (since these are numeric fields).
When the user entry is blank (by pressing the <del> key), all the records
should be selected, since no criterion is specified.
Suprisingly, this works fine for data columns containing text data and not
numeric data!
My problem is formulas under C2, D2 and E2. If I enter the formula
=IF(ISBLANK(B3),"",B3), and the cells C3, D3 & E3 are blank, onlly database
count isshowing zero.
If I use "=" instead of "", only the no. of blank records are displayed, and
if ">0" is used, only count of non-blank records are returned.
Unfortunately, there is no excel function to enter a "blank" in a cell like
NA(), except the usual "" which doesn't make the cell blank.
I wish there is a BLANK() function to be used in a formula to simulate a
blank cell!
I could find a solution when the data contains ONLY ONE numeric filed, by
using two rows for formulas and including them Qry_Criterion range.
*********************************
My database contains three numeric fields!
*********************************
Executing a macro by clicking a command button (after criteria is entered)
and updaing the formulas in Row 2 is a solution; but clumsy.
What is the formula to be used under C2, D2 & E2, so that the database count
is updated automatically the moment the user enters his criteria?
Thanks
Kripa Venkatesh
|
|
Hi,
This worked for me
=IF(D3<>"",D3,"")
Where D3 contained the user entry.
When I clear D3 I get a total count. Remember clearing the cell with a
spacebar is not the same as clearing it with Del.
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"Kripa" <kripa_venkatesh[ at ]vsnl.net> wrote in message
news:ejLelupaJHA.5676[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > I have stored some data in sheet named "Data" in database format with
> following headers:
> Column Heading Type of data
> ===== ====== ========
> A Supplier_Name Text
> B Machine_Model Text
> C Table_Length Numeric
> D Table_Width Numeric
> E Spindle_Diameter Numeric
> Some of the data under columns C, D & E are blank. Columns. A:D is named
> as "Data"
>
> The criterion for quering the database is setup as follows in another
> sheet named "Query":
>
> Col. A Col.B
> Col.C Col.D Col.E
> Row1 Supplier_Name Machine_Model
> Table_Length Table_Width Spindle_Diameter
> Row2 =IF(ISBLANK(A3),"",A3) =IF(ISBLANK(B3),"",B3)
> <----------------Problem here-------------------------->
> Row3
>
> The range A1:E2 is defined as Query_Critrerion. Row 2 is hidden and user
> input is picked up in Row 3; the formulas under Row 2 convert the user
> input for defining the criteria.
> Then I use the formula =DCOUNTA(Data,1,Qry_Criterion) in a cell in the
> "Query" sheet to get the count of records matching the criteria entered.
> The moment user types in his creiteira, immediaely, the cell displays the
> record count.
>
> The user should be allowed to input expressions like >5, >=10, etc. under
> C3, D3 & D3 (since these are numeric fields).
> When the user entry is blank (by pressing the <del> key), all the records
> should be selected, since no criterion is specified.
> Suprisingly, this works fine for data columns containing text data and not
> numeric data!
>
> My problem is formulas under C2, D2 and E2. If I enter the formula
> =IF(ISBLANK(B3),"",B3), and the cells C3, D3 & E3 are blank, onlly
> database count isshowing zero.
> If I use "=" instead of "", only the no. of blank records are displayed,
> and if ">0" is used, only count of non-blank records are returned.
> Unfortunately, there is no excel function to enter a "blank" in a cell
> like NA(), except the usual "" which doesn't make the cell blank.
> I wish there is a BLANK() function to be used in a formula to simulate a
> blank cell!
>
> I could find a solution when the data contains ONLY ONE numeric filed, by
> using two rows for formulas and including them Qry_Criterion range.
> *********************************
> My database contains three numeric fields!
> *********************************
>
> Executing a macro by clicking a command button (after criteria is entered)
> and updaing the formulas in Row 2 is a solution; but clumsy.
>
> What is the formula to be used under C2, D2 & E2, so that the database
> count is updated automatically the moment the user enters his criteria?
>
> Thanks
>
> Kripa Venkatesh
>
|
|
|