Stinking Excel....

[Highlander179]

Why wont this work?

= IF_(_39234_<_TODAY_()_<_39340_,_10%_,_0_)
with the _ removed

or this?

=IF(AND(K1>6/1/2007,K1<9/15/2007),10%,0)
K1=9/01/2007

and why wont that show up properly?

[ErVikingo]

Try this:

=IF(AND(K1>DATE(2007,6,1),K1

__________________
International GT Champion; Porsche GT3 Cup Trophy Champion; Klub Sport Challenge Champion; Rolex Vintage Endurance Series Champion; PCA Club Racing Champion; National Vintage Racing Champion

---

Last edited by ErVikingo; 05-31-2007 at 12:17 PM..

[ErVikingo]

for some reason when I post the formula it gets truncated. Emailing you....

[rick-l]

Originally posted by Highlander179
Why wont this work?

39234_<_TODAY_()_<_39340

Is that a valid logical test in XL?


today is 39233 also


edit does this work?

=IF(AND(39234_<_TODAY_()_,TODAY_()_<_39340),10%,0)

[red-beard]

the second equation works, but you need to use the numeric values for the dates, not the formated version, as it is being interpreted as a math equation instead of a date.

[Highlander179]

Originally posted by red-beard
the second equation works, but you need to use the numeric values for the dates, not the formated version, as it is being interpreted as a math equation instead of a date.

yup, that was the problem I wasn't seeing. thanks

here's what I went with:
= IF ( AND ( TODAY ( ) > = DATE ( 2007 , 6 , 1 ) , TODAY ( ) < = DATE ( 2007 , 9 , 15 ) ) , 10% , 0 )

[Highlander179]

OK, so I got another brain-teaser, for me, at least.

I have a worksheet named "Order Form"; column A is order quantity and column B is part number, and the rest of the columns are for misc. stuff like UPC, MOQ, weights, measurements, msrp, sell price that customers use for information.

I want to strip out the data for order qty and item number and put it onto a new worksheet called... lets say its called "EDI".

so, on this new worksheet I have a formula that reads = B1 and another that reads = C1 which basically pulls info to it. There are 9000 items, so then once a order is entered on the order form, I go to the EDI tab and filter for every order qty greater than zero. submit form.

NOW TO MY QUESTION:
is there a way to reverse filter a pre-filtered list so that once an order qty has been entered, the corresponding line "infilters itself" or do you guys have any other ideas that may make this "order entry" process as step-less as possible?

[Oracle]

Originally posted by Highlander179
do you guys have any other ideas that may make this "order entry" process as step-less as possible?

I'm not an Excel expert but I know when users are over-using a spreadsheet, this is one clear example when I sit with the user and explain that there are other software capable of doing all that and more.

My suggestion to you is to consider talking to someone with SQL knowledge and I'm pretty sure a database will give you everything you need.

Let's not forget that Excel is a mighty program but there are specialized software for other stuff.

[rick-l]

Originally posted by Highlander179
NOW TO MY QUESTION:
is there a way to reverse filter a pre-filtered list so that once an order qty has been entered, the corresponding line "infilters itself" or do you guys have any other ideas that may make this "order entry" process as step-less as possible?

I'd post-filter the pre-filtered filtered input.

But seriously I have no clue what you're trying to do.

[Christien]

If you're stuck and don't get answers here, try the forum at mrexcel.com - I've been stumped several times trying to do (what seems to me to be) extremely complex things in Excel (I also hate it, but realize its usefulness). The forum over there is slow, but good. It sometimes takes a couple days to get the right answer, but I've always got one eventually.

[Highlander179]

Originally posted by Oracle
My suggestion to you is to consider talking to someone with SQL knowledge and I'm pretty sure a database will give you everything you need.

Oh I hear ya, but this company is waaay too old school and cheap to boot. We manufacture artists' grade oil paints with 150 year old formulas the way the old masters use to(we are the old masters). Just getting them to use excel took leaps and bounds. They wont even think of bringing in an IT Admin. Its a highly profitable English company run on shoe strings and Band-Aids.