Named Range REPOST

[Scottie]

Date 1 2 3 4 5 6 7 8 ...ect..
Employee NameRecord * <-formula in every column with 1 digit result



*
=IF(ISNA(AND(takeoff<=G$86,takeoff>=$G$86,$B87<>"")),"",IF(AND(takeoff<=G$86,takeoff>=$G$86),"",IF(ISNA(VLOOKUP($B87&G$86&$F87,ReqOff,3,FALSE)),IF(ISODD(G$84),IF(VLOOKUP(first_name,staff_data,FSOS+G$83,FALSE)=0,"",VLOOKUP(first_name,staff_data,FSOS+G$83,FALSE)),IF(VLOOKUP(first_name,staff_data,FSES+G$83,FALSE)=0,"",VLOOKUP(first_name,staff_data,FSES+G$83,FALSE))),IF(VLOOKUP(first_name&G$86&$F87,ReqOff,4,FALSE)=0,"",VLOOKUP(first_name&G$86&$F87,ReqOff,4,FALSE)))))

G$86 = Date
G$83 = Week Number
$B87 = Current Shift

That is the formula I am using. Right now it is working relatively okay, it
tends to freak out when I edit it in 2007 while using compatability mode
97-2003.

So the question here is....how can I replace $G$86 with a named range that
will fill across the month. I really need to shorten some of the formula...I
think that will help.

Thanks alot for helping me

Scottie

 

[Bob Phillips]

what does ... fill across the month ... mean?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

news:[email protected]...

 

[Dave]

Hi Scottie,
Your question:
how can I replace $G$86 with a named range that will fill across the month?
Do you really mean $G86? If you need this to fill across (or down) then you
can't replace it with a named range. You could replace $G$86 with a named
range, but you only have 2 instances of this, so not much point.
To make the formula physically shorter, you could change some of your named
ranges, eg first_name could be changed to Nm1, takeoff could be Toff, and
staff_data could be Sdata.
You can also replace all the FALSE arguments with 0 (zero).
This won't address the 2007 compatibility issue, but will make it look a
little less scary.
Regards - Dave.

 

[Scottie]

Dave,

Can you tell me, how does the $ effect my named ranges?

using named range : date_column=First!$G$86:$AK$86 will return the correct
date
but using named range: week_num=First!$G$84:$AK$84 will return errors
and this formula also returns a #value error : =weeknum(date_column)

.....how come?

 

[Dave]

Hi Scottie,
$ signs in your named ranges:
You really need to have these, otherwise very weird things happen. The named
range reference becomes relative to the active cell - usually very
undesirable.

week_num=First!$G$84:$AK$84:
I can't see any reason why this should return errors. What error? Perhaps
you're using week_num in a place that requires a single cell reference.

=weeknum(date_column) produces #value error:
I run xl2000. Is weeknum a function that only exists in later versions? I
don't have it in my list. Or is it a typo for week_num?

Hope this helps.
Dave.

 

[Scottie]

=weeknum() is an excel function

I have tried it three ways and only one way works

I have a range of cells that goes from First!$G$84:$AK$84 that contains the
weeknumber...the range is called week_num. If I try and use week_num in my
formula it returns (#value). If I try and use the excel function:
=weeknum(date_column) it still returns value. the only way that works is
=weeknum(g$86). I am trying to remove all the absolute references from the
formula. Don't seem to be getting it yet

And I agree with hat you said " Perhaps you're using week_num in a place
that requires a single cell reference." but why would the date_column
references be working then?

 

[Dave]

Hi,
If weeknum() returns a number between 1 and 52 corresponding to a date (I'm
guessing here) then you can only give it a single date to work with, which is
why G$86 works, (a single reference) but Week_num and date_column don't,
since they are arrays which contain multiple dates, which the function is not
designed to handle.

By the way, G$86 is not an absolute reference; it's only half absolute. When
you fill across, it will become H$86, I$86, J$86 etc. Absolute references,
which have $ signs in front of both Row and Column designators (eg $A$1) do
not change when they are filled across or down. These can be changed for
named ranges. Columns or rows of data which contain non-absolute references
can also be a named range, but if you need to keep filling across (or down)
as time goes on, you would have to keep redefining your named ranges, or use
dynamic named ranges, (which are very cool!)

Can't answer your last question without seeing how you're using week_num and
date_column, and knowing the actual ranges those named ranges represent.

Regards - Dave.