how do I create cell validation for format ww yyyy only?

[tcol]

Hi All,
has anybody got a suggestion as to how I can create cell validation for format ww yyyy only (excel 2003)?

regards
tcol

 

[GlennUK]

=(value(right(b2,4))-value(right(a2,4)))*52+left(b2,2)-left(a2,2)

 

[tcol]

cheeers Glenn I owe you a beer........

 

[Peter_SSs]

Just as VALUE is not required with the LEFT() calculations, it is not required with the RIGHT calculations either.

=(RIGHT(B2,4)-RIGHT(A2,4))*52+LEFT(B2,2)-LEFT(A2,2)

We did allow 53 in the Data Validation, could that be an issue?

 

[GlennUK]

Just as VALUE is not required with the LEFT() calculations, it is not required with the RIGHT calculations either.

=(RIGHT(B2,4)-RIGHT(A2,4))*52+LEFT(B2,2)-LEFT(A2,2)

We did allow 53 in the Data Validation, could that be an issue?

Yeah, I automatically put the values in from habit ... well spotted ... again!!

Just to note, most systems I've worked on where week number is used go up to 53, due to partial weeks at year start and end ( most years ). This case may be different of course.

 

[tcol]

cheers guys,
in our case 52 weeks is fine. I have another issue.......
This is an offset formula that we use in conjunction with defined names
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
=OFFSET('Active Capsule_Fill_Proj'!$C$2,0,32,COUNTA('Active Capsule_Fill_Proj'!$C$2:$C$9947),1)
<o> </o>
Unfortunately it is not dynamic in the sense that if I add in an additional column into the spreadsheet, the no. in red has to be manually updated, as the counts are linked to graphs & they are out of kilter also.
<o> </o>
Is there a better way of doing this? like getting excel to follow a defined name or range around regardless of where it is moved to

 

[Peter_SSs]

If the 32 in your current formula is the offset to the rightmost column as determined by looking for a heading (text) in row 1 of 'Active Capsule_Fill_Proj', then try replacing the 32 in your current formula with:

MATCH(REPT("z",255),'Active Capsule_Fill_Proj'!1:1)-COLUMN('Active Capsule_Fill_Proj'!$C$2)

 

[tcol]

HI Peter thanks

when I did as you suggested, the cell range reference did change but not to those corresponding to the defined name...........

regards
tcol

 

[Peter_SSs]

As far as I can see you haven't said what your defined name is or how it is defined.

 

[tcol]

HI Peter
the range that the defined name refers to is represented by the offset formula above.
can I build in the defined name into the offset formula or is there another way?


regards
tcol

 

[Peter_SSs]

the range that the defined name refers to is represented by the offset formula above.
can I build in the defined name into the offset formula or is there another way?

I don't understand that, it seems circular.

Re-wording your statements slightly, I think they say this:
The defined name refers to the range represented by the OFFSET formula.
Can I build the defined name into the offset formula?

Isn't that the same as saying:
Can I build the defined name into the defined name?


In any case, I defined a range called 'myRange' as I described in my earlier post:

=OFFSET('Active Capsule_Fill_Proj'!$C$2,0,MATCH(REPT("z",255),'Active Capsule_Fill_Proj'!1048558:1048558)-COLUMN('Active Capsule_Fill_Proj'!$C$2),COUNTA('Active Capsule_Fill_Proj'!$C$2:$C$9947),1)

On another sheet I was able to use a formula like
=SUM(myRange)
to sum cells on sheet 'Active Capsule_Fill_Proj' in the column with the right-most text heading in row 1. Isn't that the sort of thing you are tring to do?

For example, if 'Active Capsule_Fill_Proj' is as shown below and with 'myRange' defined as above, the formula =SUM(myRange) returns 11 (the sum of the yellow cells).

If I add text in cell K1 then the SUM formula returns 8.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	H1	H2	H3	H4	H5	H6	H7	H8	H9	H10
2			x								2
3			x							6	2
4			x							3	2
5			x
6			x
7			x
8			x
9			x
10			x
11			x							2	2
12										600	400
13
Active Capsule_Fill_Proj