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

tcol

New Member
Joined
Mar 8, 2011
Messages
19
Hi All,
has anybody got a suggestion as to how I can create cell validation for format ww yyyy only (excel 2003)?

regards
tcol
 
Code:
=(value(right(b2,4))-value(right(a2,4)))*52+left(b2,2)-left(a2,2)
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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?
 
Upvote 0
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.
 
Upvote 0
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-com:eek:ffice:eek:ffice" /><o:p> </o:p>
=OFFSET('Active Capsule_Fill_Proj'!$C$2,0,32,COUNTA('Active Capsule_Fill_Proj'!$C$2:$C$9947),1)
<o:p> </o:p>
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:p> </o:p>
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
As far as I can see you haven't said what your defined name is or how it is defined.
 
Upvote 0
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
 
Upvote 0
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
ABCDEFGHIJKL
1H1H2H3H4H5H6H7H8H9H10
2x2
3x62
4x32
5x
6x
7x
8x
9x
10x
11x22
12600400
13
Active Capsule_Fill_Proj
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top