custom list?

  • Thread starter Thread starter natei6
  • Start date Start date
N

natei6

Hi all

I have a parts list on sheet 1. Column A is Length, Column B is Width
Column C is Quanity Column D is Description. Say I have 100 rows i
sheet 1 and only a few have a number (quanity) greater than 1 in colum
C. Question: Is there a formula to create a custom list on sheet 2 tha
only shows parts with quanities greater than 0 all in consecutiv
rows
 
Hi

Be sure you have a header row in your table on Sheet1. Save the workbook.
Create a named range (Insert.Name.Define), p.e. MyTable. The named range
MUST contain at leas all filled rows in your table, and I'm not sure about
it, but better don't use dynamic range. So the range will be something like
=Sheet1!$A$1:$D$500

On Sheet2, select the cell, from where you want imported data to start, and
then select from menu Data.NewDatabaseQuery, from Databases Tab select Excel
files, select your workbook as database and press OK.
Now select MyRange (or what you named your table range) as table, select all
fields, set condition to Quantity>1, and finish. A table in inserted at your
cursor location with data you need.

When you edit table on Sheet1, and want to refresh the table on Sheet2,
select any cell in datarange, returned earlier on Sheet2, and select from
menu Data.RefreshData
 
Hi,

Everthing you said worked fine except it will not show any values i
the quanity column. The Quanity is generated by formulas in sheet 1
Any suggestions appreciated.

TIA

Nathan Sargean
 
Hi

I tested it with the setup you described, with formulas in Quantity column
returning number or "". For me the query worked, when I had the criteria for
Quantity either as
0
Click to expand...
or
Is Not Null

What formula are you using in Quantity column? Or maybe you send me your
Excel workbook (Packed with winzip - our mailserver don't pass unpacked
excel files. And when your formulas in Quantity column, or formulas
elsewhere referred in Quantity column to, contains links to some another
workbook, then forget it!) on my email (the one from signature).
 
Hi

It didn't arrive jet - and a hour is past. Are you sure you used right
address - inserted (e-mail address removed) into To: field manually. When you did use
automatic reply address, then your mail was deleted immediately, when
arrived on mail server. And be sure you have some meaningful subjekt marked
too - I never open mails with something like 'Hi' or 'Look at this' in
subject line. Thanks me using my real mail address here in NGs until August
of last year, over 100 mails per day, full with spam or viruses, are sent on
my old mail address I haven't used for 8 months now. (I have to keep it as
alias, or remove the old address from address books in nearly 100 computers
manually)
 
Arvi said:
*Hi

I tested it with the setup you described, with formulas in Quantit
column
returning number or "". For me the query worked, when I had th
criteria for
Quantity either as
or
Is Not Null

What formula are you using in Quantity column? Or maybe you send m
your
Excel workbook (Packed with winzip - our mailserver don't pas
unpacked
excel files. And when your formulas in Quantity column, or formulas
elsewhere referred in Quantity column to, contains links to som
another
workbook, then forget it!) on my email (the one from signature).
Click to expand...

There is no formula on sheet 2, but the quanities on sheet one ar
generated by formulas. The Quantity column on sheet 2 remains blank.
Is an option set wrong in excell
 
Hi


natei6 > said:
There is no formula on sheet 2, but the quanities on sheet one are
generated by formulas. The Quantity column on sheet 2 remains blank.
Is an option set wrong in excell?
Click to expand...

Post your Quantity formula p.e. for cell C2 here

Arvi Laanemets
 
These are the formulas in sheet 1. It will display the results in shee
2 when certain ones are greater than zero, then it does everthing as
expected, but when certain ones equal zero, All quantities become blan
in sheet 2. Why?


=IF(A8>0,(A8),"")
=IF(A7>0,(A7),"")
=IF(A34>0,(A34),"")
=IF(A34>0,(A34*2),"")
=IF(SUM(A17+A21)>0,SUM(A17+A21)*2,"")
=IF(A23>0,(A23),"")
=IF(SUM(A15+A20)>0,SUM(A15+A20)*2,"")
=IF(A22>0,(A22),"")
=IF(A26>0,(A26),"")
=IF(A16>0,(A16),"")
=IF(A6>0,(A6),"")
=IF(A27>0,(A27),"")
=IF(SUM(A10+A14+A19)>0,SUM(A10+A14+A19)*2,"")
=IF(SUM(A18+A19+A20+A21)>0,SUM(A18+A19+A20+A21),"")
=IF(A11>0,A11,"")
=IF(A34>0,A34,"")
=IF(A34>0,A34,"")
=IF(A11>0,A11,"")
=IF(A11>0,A11,"")
=IF(A11>0,A11*1/2,"")
=IF(SUM(A9+A13+A16+A18+A27)>0,SUM(A9+A13+A16+A18)*2+A27,"")
=IF(A27>0,A27,"")
=IF(SUM(A32+A33)>0,SUM(A32+A33),"")
=IF(SUM(A32+A33)>0,SUM(A32*2)+(A33*3),"")
=IF(A24>0,A24,"")
=IF(SUM(A13+A14+A15+A17)>0,SUM(A13+A14+A15+A17),"")
=IF(SUM(A28+A29+A30+A31)>0,SUM(A28+A29+A30+A31),"")
=IF(A25>0,A25*3,"")
=IF(A25>0,A25,"")
=IF(A24>0,A24*2,"")
=IF(SUM(A28+A29+A30+A31)>0,SUM(A28+A29)+SUM(A30+A31)*2,"")
=IF(A9>0,A9,"")
=IF(A12>0,A12*2,"")
=IF(SUM(A10+A12)>0,SUM(A10+A12),"")
=IF(SUM(A6+A7+A8)>0,SUM(A6+A7+A8)*2,"")
=IF(A16>0,A16,"")
=IF(A18>0,A18,"")
=IF(SUM(A19+A20+A21)>0,SUM(A19+A20+A21),"")
=IF(A27>0,A27,"")
=IF(SUM(A14+A15+A17)>0,SUM(A14+A15+A17),"")
=IF(A13>0,A13,"")
=IF(A9>0,A9,"")
=IF(SUM(A10+A12)>0,SUM(A10+A12),"")
=IF(SUM(A28+A29+A30+A31)>0,SUM(A28+A29+A30+A31),"")
=IF(SUM(A28+A29+A30+A31)>0,SUM(A28+A29)+SUM(A30+A31)*2,"")
=IF(SUM(A18+A19+A20+A21)>0,SUM(A18*3)+(A19*4)+(A20*5)+(A21*6),"")
=IF(A34>0,(A34)*2,"")
=IF(SUM(A13+A14+A15+A17)>0,SUM(A13*3)+(A14*4)+(A15*5)+(A17*6),"")
=IF(A16>0,A16*6,"")
=IF(A9>0,A9*4,"")
=IF(A27>0,A27,"")
=IF(SUM(A10+A12+A27)>0,SUM(A10*4)+(A12*2)+(A27*3),""
 
See about nesting the formulas in an iserror function to allow for 0s or try
using a text output to trouble shoot the formula. I see a lot of "" in the
formula to me the answer simply is the cell in the formula is not > 0 as in
your formula =IF(A8>0,(A8),"") put a "NG" or something. Also, try nesting
some formulas for example your formula
=IF(SUM(A17+A21)>0,SUM(A17+A21)*2,"") would be like this
=IF(ISERROR(SUM(A17+A21)*2),"No Numbers", (SUM(A17+A21)*2)). Finally are
these on a seperate sheet than where the values are located because I see no
reference to the sheet in the workbook used in the formula for example your
formula =IF(A8>0,(A8),"") if it was on Sheet2 trying to process a value on
Sheet1 should read like this =IF(Sheet1!A8>0,Sheet1!A8,"").
 
Hi,

I don't have any formulas on sheet two, I'm only making a query for th
results of those formulas on sheet two if they are greater than zero
The problem arises when I refresh data on sheet two. Sometimes all i
well, somtimes my quanities are blank, it's all or nothing. Anymor
suggestions?

Thanks
Nathan Sargean
 
Would this be a Pivot Table? - If so right click on a cell in it and choose
refresh.
 
Hi

What is returned on first datarow on sheet1 (in cell C2)?

ODBC driver decides about field type by first entry. When this is "", then
it interprets all data in column as strings, and setting the WHERE clause of
query to '>0' returns 0 rows. And more of that - when you afterwards change
the entry in C2 to some number, you'll get 'Wrong data type' error when
running query. But this doesn't return an empty Quantity column, with rest
of data present!

Anyway, you have to be sure, that in first datarow in numeric columns always
are values>0, or you have to change you formulas like:
=A8
=A7
=A34
=2*A34
etc.

About your case - try this:
Copy your sheet1 (right-click on sheet tab, Copy Sheet, check 'Create a
copy)
On copyed sheet, convert formulas in Quantity column to values (use
PasteSpecial for it)
Create a query, using copy of sheet1 as source.
When you get all right with query, start replacing values with formulas,
refreshing the query after every replacement - maybe so you can find the
cause for the problem.
 
Thankyou,

You helped, it was the way the formula was that caused the problem.

Nathan Sargea
 
Thankyou,

You helped, it was the way the formula was that caused the problem.

Nathan Sargean
 
Back
Top