RE: lookup problems

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Biff (biffinpitt_at_comcast.net)
Date: 11/05/04 

Date: Thu, 4 Nov 2004 18:49:53 -0800

Hi!

>=IF(ISERROR(F2),TRUE,FALSE)

You don't need all that. You can shorten it to:

=ISERROR(F2)

Good luck!

Biff

>-----Original Message-----
>Ok I figured it out:
>
>=IF(ISERROR(F2),TRUE,FALSE)
>
>I'm just a little slow, i'll get it eventually. Thanks
for all your help.
>This was the first time i ever used the online help/chat
area.
>
>O'C
>
>
>"O'C" wrote:
>
>> I came up with this one today and it works like a
champ. Now the only
>> problem i have is with the #num! error showing up. I
think I'm against the
>> wall in the nested functions area because i keep
getting an error in my
>> formula when i try inserting the old IF(ISERROR(INDEX
(...),"",INDEX(...). I
>> can't figure out how to hide with the CONDIT
Formatting. Got any ideas???
>>
>> =INDEX(TRUCKS!$C$1:$C$21,SMALL(IF(TRUCKS!$A1:$A22=***!
$A$12,IF(TRUCKS!$B$1:$B$22<=DATE(2005,MONTH(F1),31),IF
(TRUCKS!$B$1:$B22>=DATE(2005,MONTH(F1),1),ROW
($A$1:$A$21),""),""),""),ROW($A$1:$A$21)))
>>
>> Thanks,
>> O'C
>>
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > Ok, so you allot 6 rows for 6 possible items per
vehicle
>> > per month, no problem.
>> >
>> > array entered:
>> >
>> > =INDEX($Q$1:$Q$10,SMALL(IF(($O$1:$O$10=$A$2)*(MONTH
>> > ($P$1:$P$10)=MONTH(B$1))=1,ROW($A$1:$A$10)),ROW(1:1)))
>> >
>> > Now, this will take some tweaking because you'll have
to
>> > manually change the reference cell to each vehicle
instead
>> > of just being able to create one formula and drag
copying
>> > it. You can copy this formula across then down for
the
>> > first vehicle only. Then you would need to change the
>> > reference cell to vehicle 2.
>> >
>> > ROW($A$1:$A$10) refers to the length of your raw data
>> > list. If your actual list is 2200 rows long just
change
>> > the range reference to A1:A2200. If you don't know
the
>> > exact length and don't feel like counting it you can
use
>> > something like this:
>> >
>> > ROW(INDIRECT("1:"&COUNTA(Q:Q)))
>> >
>> > As for the revolving calender thing, ???????
>> >
>> > You may be better off getting some preventative
>> > maintenance scheduling software.
>> >
>> > Biff
>> >
>> > >-----Original Message-----
>> > >I tried it and it works very much like the several
>> > VLOOKUPs i've done. The
>> > >real problem i have is that at times there are
several
>> > items due each month
>> > >and this only brings up the first item due. I'm
dealing
>> > with with 14
>> > >vehicles each with 150-160 inspections due at
various
>> > times. I have a data
>> > >miner that strips a txt file and imports a 2200 row
by 41
>> > col data set. I'm
>> > >currently using 6 rows per vehicle per month and
manually
>> > typing the data.
>> > >What i need is excel to save me some time and do it
for
>> > me. I've always had
>> > >the problem of finding the next item due in a list
of
>> > items due on thew same
>> > >vehicle in the same month. Another thing is that i
have
>> > to use a revolving
>> > >calendar starting at the current month and going out
for
>> > the year.
>> > >
>> > >HELP,
>> > >O'C
>> > >
>> > >"Biff" wrote:
>> > >
>> > >> Hi!
>> > >>
>> > >> This can be done if you set it up properly. It
would
>> > also
>> > >> require a lot of formulas (depending on how many
>> > vehicles
>> > >> you have)- 12 * number of vehicles.
>> > >>
>> > >> To make things as easy as possible the dates in
your
>> > >> columnar data and the 12 months in the calander
must be
>> > >> true Excel dates.
>> > >>
>> > >> For the 12 calander months you can enter any date
of a
>> > >> particular month and just format the cells as MMM.
For
>> > >> example, you can enter 1/1 for January and then
format
>> > as
>> > >> MMM to give you the displayed value of Jan.
>> > >>
>> > >> For the purposes of this example I will use this
sample
>> > >> data as your list and it's located in the range
O1:Q10
>> > >>
>> > >> 1 1-Jan brake
>> > >> 2 5-Nov tune
>> > >> 3 6-Oct insp
>> > >> 4 1-Feb insp
>> > >> 5 1-Mar tune
>> > >> 6 1-Apr brake
>> > >> 7 1-May align
>> > >> 8 1-Aug muff
>> > >> 9 1-Sep tires
>> > >> 10 1-Jun oil
>> > >>
>> > >> Now, assume that in your "calander" the vehicle
ID's
>> > start
>> > >> in A2. The months are listed in B1:M1.
>> > >>
>> > >> In B2 enter this array formula using the key combo
of
>> > >> CTRL,SHIFT,ENTER:
>> > >>
>> > >> =INDEX($Q$1:$Q$10,MATCH(1,($O$1:$O$10=$A2)*(MONTH
>> > >> ($P$1:$P$10)=MONTH(B$1)),0))
>> > >>
>> > >> Copy across then down to fill the calander. You
will
>> > get a
>> > >> lot of #N/A errors where there is no matching
data. You
>> > >> can either suppress the #N/A's from being
displayed by
>> > use
>> > >> of the formula or you can hide the #N/A's using
>> > >> conditional formatting. Personally, I would use cf.
>> > >>
>> > >> Biff
>> > >>
>> > >> >-----Original Message-----
>> > >> >I'm trying to create a calendar spead*** from a
list
>> > of
>> > >> information. Let's
>> > >> >say my data is in the form of three COL. The
first is
>> > a
>> > >> vehicle ID, second
>> > >> >is a due date, and lastly number three is what
work is
>> > >> due for that vehicle
>> > >> >on that date. I'm trying to bring it over into a
>> > >> spreed*** with the
>> > >> >vehicles listed in down in col 1 then the months
>> > spread
>> > >> accorross the top
>> > >> >from Jan in col 2 through Dec in col 13. Any
help you
>> > >> can give me is greatly
>> > >> >appreciated.
>> > >> >.
>> > >> >
>> > >>
>> > >.
>> > >
>> >
>.
>

Quantcast