Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
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(I SNA(VLOOKUP($B87&G$86&$F87,ReqOff,3,FALSE)),IF(ISO DD(G$84),IF(VLOOKUP(first_name,staff_data,FSOS+G$8 3,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,FSE S+G$83,FALSE))),IF(VLOOKUP(first_name&G$86&$F87,Re qOff,4,FALSE)=0,"",VLOOKUP(first_name&G$86&$F87,Re qOff,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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
what does ... fill across the month ... mean?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Scottie" wrote in message ... 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(I SNA(VLOOKUP($B87&G$86&$F87,ReqOff,3,FALSE)),IF(ISO DD(G$84),IF(VLOOKUP(first_name,staff_data,FSOS+G$8 3,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,FSE S+G$83,FALSE))),IF(VLOOKUP(first_name&G$86&$F87,Re qOff,4,FALSE)=0,"",VLOOKUP(first_name&G$86&$F87,Re qOff,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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
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" wrote: 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(I SNA(VLOOKUP($B87&G$86&$F87,ReqOff,3,FALSE)),IF(ISO DD(G$84),IF(VLOOKUP(first_name,staff_data,FSOS+G$8 3,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,FSE S+G$83,FALSE))),IF(VLOOKUP(first_name&G$86&$F87,Re qOff,4,FALSE)=0,"",VLOOKUP(first_name&G$86&$F87,Re qOff,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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
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" wrote: 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" wrote: 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(I SNA(VLOOKUP($B87&G$86&$F87,ReqOff,3,FALSE)),IF(ISO DD(G$84),IF(VLOOKUP(first_name,staff_data,FSOS+G$8 3,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,FSE S+G$83,FALSE))),IF(VLOOKUP(first_name&G$86&$F87,Re qOff,4,FALSE)=0,"",VLOOKUP(first_name&G$86&$F87,Re qOff,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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
=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" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range REPOST
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repost: seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
repost: plz help- dynamic range with gaps? | Excel Discussion (Misc queries) |