formula #63

i have a list of postcodes that i need to be able to pre select the zone ie 
1-10
example
AB = 1
PE = 3
0
MARTIN1 (286)
9/8/2005 11:51:08 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
669 Views

Similar Articles

[PageSpeed] 10

martin Wrote: 
> i have a list of postcodes that i need to be able to pre select the zone
> ie
> 1-10
> example
> AB = 1
> PE = 3

Hi Martin

You probably need a lookup table

Need a bit more info to give a better response

Post some example data


-- 
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=465872

0
9/8/2005 12:55:24 PM
Thanks Paul
When I recieve data i need the postcde to automaticaly select the zone
Please see below of the specific postcodes and their Zone, ie AB is zone 7 


AB	7
AL	2
B	2
BA	4
BB	5
BD	4
BH	4
BL	4
BN	3
BR	2
BS	4
CA	5
CB	1
CF	5
CH	4
CM	2
CO	2
CR	2
CT	3
CV	2
CW	4
DA	2
DD	7
DE	3
DG	6
DH	5
DL	5
DN	4
DT	4
DY	3
E	2
EC	2
EH	6
EN	2
EX	5
FK	7
FY	5
G	6
GL	4
GU	3
HA	2
HD	4
HG	5
HP	2
HR	4
HU	4
HX	4
IG	2
IP	2
IV	7
KA	6
KT	2
KW	7
KY	7
L	4
LA	5
LD	5
LE	2
LL	6
LN	3
LS	4
LU	1
M	4
ME	3
MK	1
ML	6
N	2
NE	5
NG	2
NN	1
NP	4
NR	2
NW	2
OL	4
OX	2
PA	6
PE	2
PH	7
PL	6
PO	3
PR	5
RG	3
RH	3
RM	2
S	3
SA	5
SE	2
SG	1
SK	4
SL	2
SM	2
SN	3
SO	3
SP	3
SS	2
ST	3
SW	2
SY	3
TA	5
TD	6
TF	3
TN	3
TQ	6
TR	6
TS	5
TW	2
UB	2
W	2
WA	4
WC	2
WD	2
WF	4
WN	4
WR	2
WS	2
WV	3
YO	5




"martin" wrote:

> i have a list of postcodes that i need to be able to pre select the zone ie 
> 1-10
> example
> AB = 1
> PE = 3
0
MARTIN1 (286)
9/8/2005 1:23:03 PM
martin Wrote: 
> Thanks Paul
> When I recieve data i need the postcde to automaticaly select the zone
> Please see below of the specific postcodes and their Zone, ie AB is
> zone 7
> 
> 
> AB	7
> AL	2
> B	2
> BA	4
> BB	5
> BD	4
> BH	4
> BL	4
> BN	3
> BR	2
> BS	4
> CA	5
> CB	1
> CF	5
> CH	4
> CM	2
> CO	2
> CR	2
> CT	3
> CV	2
> CW	4
> DA	2
> DD	7
> DE	3
> DG	6
> DH	5
> DL	5
> DN	4
> DT	4
> DY	3
> E	2
> EC	2
> EH	6
> EN	2
> EX	5
> FK	7
> FY	5
> G	6
> GL	4
> GU	3
> HA	2
> HD	4
> HG	5
> HP	2
> HR	4
> HU	4
> HX	4
> IG	2
> IP	2
> IV	7
> KA	6
> KT	2
> KW	7
> KY	7
> L	4
> LA	5
> LD	5
> LE	2
> LL	6
> LN	3
> LS	4
> LU	1
> M	4
> ME	3
> MK	1
> ML	6
> N	2
> NE	5
> NG	2
> NN	1
> NP	4
> NR	2
> NW	2
> OL	4
> OX	2
> PA	6
> PE	2
> PH	7
> PL	6
> PO	3
> PR	5
> RG	3
> RH	3
> RM	2
> S	3
> SA	5
> SE	2
> SG	1
> SK	4
> SL	2
> SM	2
> SN	3
> SO	3
> SP	3
> SS	2
> ST	3
> SW	2
> SY	3
> TA	5
> TD	6
> TF	3
> TN	3
> TQ	6
> TR	6
> TS	5
> TW	2
> UB	2
> W	2
> WA	4
> WC	2
> WD	2
> WF	4
> WN	4
> WR	2
> WS	2
> WV	3
> YO	5
> 
> 
> 
> 
> "martin" wrote:
> 
> > i have a list of postcodes that i need to be able to pre select the
> zone ie
> > 1-10
> > example
> > AB = 1
> > PE = 3

Hi Martin

Assuming your data to be in cells A1:B117 and the postcode you are
looking to match a zone to in D1, then in E1 put the following formula
>

=VLOOKUP(D1,$A$1:$B$117,2,0)

Change the ranges and cell refernces to suit your data


-- 
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=465872

0
9/8/2005 9:39:34 PM
Thankyou paul

"Paul Sheppard" wrote:

> 
> martin Wrote: 
> > Thanks Paul
> > When I recieve data i need the postcde to automaticaly select the zone
> > Please see below of the specific postcodes and their Zone, ie AB is
> > zone 7
> > 
> > 
> > AB	7
> > AL	2
> > B	2
> > BA	4
> > BB	5
> > BD	4
> > BH	4
> > BL	4
> > BN	3
> > BR	2
> > BS	4
> > CA	5
> > CB	1
> > CF	5
> > CH	4
> > CM	2
> > CO	2
> > CR	2
> > CT	3
> > CV	2
> > CW	4
> > DA	2
> > DD	7
> > DE	3
> > DG	6
> > DH	5
> > DL	5
> > DN	4
> > DT	4
> > DY	3
> > E	2
> > EC	2
> > EH	6
> > EN	2
> > EX	5
> > FK	7
> > FY	5
> > G	6
> > GL	4
> > GU	3
> > HA	2
> > HD	4
> > HG	5
> > HP	2
> > HR	4
> > HU	4
> > HX	4
> > IG	2
> > IP	2
> > IV	7
> > KA	6
> > KT	2
> > KW	7
> > KY	7
> > L	4
> > LA	5
> > LD	5
> > LE	2
> > LL	6
> > LN	3
> > LS	4
> > LU	1
> > M	4
> > ME	3
> > MK	1
> > ML	6
> > N	2
> > NE	5
> > NG	2
> > NN	1
> > NP	4
> > NR	2
> > NW	2
> > OL	4
> > OX	2
> > PA	6
> > PE	2
> > PH	7
> > PL	6
> > PO	3
> > PR	5
> > RG	3
> > RH	3
> > RM	2
> > S	3
> > SA	5
> > SE	2
> > SG	1
> > SK	4
> > SL	2
> > SM	2
> > SN	3
> > SO	3
> > SP	3
> > SS	2
> > ST	3
> > SW	2
> > SY	3
> > TA	5
> > TD	6
> > TF	3
> > TN	3
> > TQ	6
> > TR	6
> > TS	5
> > TW	2
> > UB	2
> > W	2
> > WA	4
> > WC	2
> > WD	2
> > WF	4
> > WN	4
> > WR	2
> > WS	2
> > WV	3
> > YO	5
> > 
> > 
> > 
> > 
> > "martin" wrote:
> > 
> > > i have a list of postcodes that i need to be able to pre select the
> > zone ie
> > > 1-10
> > > example
> > > AB = 1
> > > PE = 3
> 
> Hi Martin
> 
> Assuming your data to be in cells A1:B117 and the postcode you are
> looking to match a zone to in D1, then in E1 put the following formula
> >
> 
> =VLOOKUP(D1,$A$1:$B$117,2,0)
> 
> Change the ranges and cell refernces to suit your data
> 
> 
> -- 
> Paul Sheppard
> 
> 
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
> View this thread: http://www.excelforum.com/showthread.php?threadid=465872
> 
> 
0
MARTIN1 (286)
9/13/2005 3:24:41 PM
Reply:

Similar Artilces:

INDEX and OFFSET Formulas
If I have a set of veritcal data for each employee, but each set of data could range from 1 to 4 vertical values, is there a way to change the following formulas to accomodate moving the data from horizontal to vertical?: =INDEX($A$2:$A$1690,1+(8*(ROWS(H$1:H1)-1))) =OFFSET($G$2,ROW(A1)*8-8+COLUMN(R2)-18,) Data Sample: ID Data 275 Capitalize on Technical and Professional Know-How 275 Foster Teamwork 275 Value Others 933 Demonstrate Agility 933 Drive for Results 933 Team Leadership 933 Use Technical/Functional Expertise 658 Capitalize on Technical and Profess...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

Delete cell value but keep formula in cell.
After the formula in a cell has calculated a value I would like to reset the cell for the next use by deleting the value but keeping the formula in the cell. Is this possible in Excel 2007? You need to explain that a bit. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Lofred" <Lofred@discussions.microsoft.com> wrote in message news:D3DE79C2-C41E-46C7-8F6C-EAD9960EC528@microsoft.com... > After the formula in a cell has calculated a value I would like to reset > the > cell for the next use by deleting the value but...

Why can't I get the Conditional Sum Wizard to work? Here is my formula:
Hey everybody, I'm probably missing something obvious, but please help me if you can. Here is my formula and now it just results in an empty cell. It won't produce a result. THANKS IN ADVANCE. =SUM(IF($C$73:$C$77="Large-Cap/Growth",IF($C$73:$C$77="Large-Cap/Value",IF($C$73:$C$77="Small/Mid-Cap/Growth",IF($C$73:$C$77="Small/Mid-Cap/Value",$D$73:$D$77,0),0),0),0)) Originally I was trying to sum a table and have the answer appear on a different worksheet and I kept getting a Runtime error 1004: unable to get the formula array property of the Range ...

Siple but what was the formula for splitting names?
Hi, I need the formula for splitting names... i.e need "Martyn Williams" in A1 to be splitted to "Martyn" and "Williams" in B1 and C1 TIA One way, assuming the names are all in a "2 word" structure: In B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1) In C1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99) ("99" is arbitrary, just choose a number high enough to extract the max likely # of characters in the 2nd word) Another easier? way to try is to use: Data > Text to columns Select A1 Click Data > Text to columns > Next In...

Copy formulas in Office 2007
Hello. A customer can't use "Copy Special" in Office 2007. He has upgradede Office 2007 from 2003. When he copy cells to another workbook, the formulas aren't copied. And when he tries the "Copy Special" a windows comes with questions about objects. He want's to be able to copy cells without formulas but with nubmer formats. I'm able to do so on several PC's. Not on his. Because of the upgrade? -- Regards, Steffen My first guess is that the different workbooks are open in different instances of excel. Close one workbook and that instance of exce...

Replicating Formulas between excel files
Hi there, Does anyone know how I can replicate formulas (verbatim) between difference excel files? I've tried to cut and paste between the separate files, but to my dismay it makes a reference to the original file. Regards, Nickchups The best procedure depends on how many you're trying to copy. For one or two, you can select the *entire* formula in the formula bar, right click and choose "Copy", then hit <Enter>. Now you can navigate to wherever you wish, and Paste it as many times and/or places you want. For numerous formulas, *unformulate* them by replacing t...

"Average" calculation formula needed
Hello, I have Excel 97, and am trying to learn formulas. I need to find the "average" of certain values in column B below. This is an example of my worksheet A B 1 TRUE 1.0% 2 FALSE -.8 3 TRUE -.5 4 FALSE -. 5 TRUE 1. 6 TRUE -. 7 FALSE . What I need: In Cell C50 I want the calculated result in the form of a "%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the average % (both positive and negative) in column "B" that are next to "T...

Formula help req'd
Hi there I am having trouble trying to display result. Basically say A1 has 10 in it and A2 has 5 in it A3 has the formula A3 =sum(A1-A2) {answer =5} What I want it do do is... say A4 displays HW as per the key. so if A1 = 2 and A2 = 7 A3 answer is -5 - A4 then displays AD and so on the key 0 to 3 = HD 4 + = HW -1 to -5 = AD -6 + = AW Any help gratefully appreciated. cheers Paul PS: I have posted in excel worksheet function forum too. Hi Paul no need normally to post in more than one ng =IF(A3>=4,"HW",IF(A3>=0,"HD",IF(A3>=...

Newbe needs help with formula...
I need a easy way to count the following cells with numbers less then 10. columns c to h, rows 2 to 20, 30 to 40 & 50 to 60 in each column. The next 5 sheets I need to do the same thing but the rows are different on each sheet. I was going to use =(COUNTIF(c2:c20,"<10"))+(COUNTIF(c30:c40,"<10"))+(COUNTIF(c50:c60,"<10"))+. ... and so on until I have all the rows and columns covered but the formula will get very long. Is there a shorter formula I could use to save from using this very long formula??? Thanks If the dimensions are identical in the...

Date numbers convert to formula
When I input the string 4/9/4 into a cell having a date format of dd/mm/yy, Excel first converts the entry into the formula =4/9/4 (or 0.111111) and then converts the result into the date 01/00/00. I assume I need to change a setting somewhere to get 04/09/04, but I have grown weary of looking for it. Thanks. The only way I can get that to happen is if I type in =4/9/4 into th cell. You should be typing 4/9/4 (no equal sign). Maybe? -- Message posted from http://www.ExcelForum.com I'm not typing the equal sign. Excel is adding it. >-----Original Message----- >The only...

Excel AddIn
Hi All, I'm working on VSTO Excel 2007 AddIn in C#. I need to call Formula Dialog and than to fetch the formula string from that dialog. Problem is that dialog doesn't offer something like dialog.FormulaText but instead inserts formula in ActiveCell. So code like this: var dlg = this.Application.Dialogs[Microsoft.Office.Interop.Excel.XlBuiltInDialog.xlDialogFunctionWizard]; dlg.Show(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, miss...

Wont show Values, just formula
Have some formulas like the following:- =IF(B10=0," ",LOOKUP(B10,Menu!$A$1:$A$146,Menu!$B$1:$B$146)) wanted to change the lookup range, but every time i change the formula it just displayes the formula, and not the resulting value. WHY?? I have checked the View Options and they are OK. Tried deleting an retyping the formula. Nothing helps Can some1 help me please ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Actually what you want to make sure you have on ...

What is the formula for series: Jan-04, Feb-04, Mar-04, etc.?
Hi Kitty, =DATE(YEAR(A1),MONTH(A1)+1,1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "kitty" <kitty@discussions.microsoft.com> wrote in message news:2C586891-2883-47B9-A3DE-AF77D4A9799E@microsoft.com... > Hi kitty, One way of doing it is with the formula below: If you have a date is cell A1 (01/01/04) and you want cell B1 to sho you the following month insert in B1 the formula below. =EOMONTH(A1,0)+1 You may have to install the "add in" the ToolPak in order for th formula to work other wise you will get a #NAME?, error. Hope thi helps -- Fa...

Freeze NOW formula
I have entered a conditional formula, that is if an adjacent cell contains a number then the NOW function will enter the current date and time into another cell. If I copy this formula in subsequent cells and enter a number into the conditional cell excel recalcs both cells, however I want the first cell to calculate once then freeze. The reason for this is i want to generate unique document numbers from exact times and dates. You can use shortcut keys to enter the current date and time: CTRL+; for the date and CTRL+SHIFT+: for the time. On Thu, 5 Aug 2004 16:07:02 -0700, "cameron&...

Conditional formula 05-29-10
Hello, I want to make all the conditions occur in cell b1: if cell a1 = 1 then cell b = 7 if cell a1 = 2 then cell b = 6 if cell a1 = 3 then cell b = 5 if cell a1 = 4 then cell b = 4 I know how to do =IF(G2=1,"7") but I can't figure out how to add the rest of the conditions to it so they are all in cell b1. I've tried a dozen different combinations of commas, brackets, colons, etc. and searching "help" isn't helpful! How do I combine all these conditions? Thank you. Sorry, I meant to say b1 for all the conditions where b occurs. So for ...

Complicated Formula #2
Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before...

Date formula rounded up
June 30, 2010 A B C D Birth Date Date of Hire Appt. Int/Act. Appt. VP November 22, 1962 September 1, 1988 N/A September 1, 1998 Total Admin Service Total Bd Service E F 11.84 21.84 I am usint June 30 , 2010 as the end date for the formula. From Sept 1,1998 to June 30 I get 11.84 years. this should be 11 years 10 months and then From Sept 1, 1988 to June 30, 2010 should be 21 years 10 months. I tried...

What is the formula for rounding a dollar amount to the nearest ni
What is the formula for rounding a dollar amount to the nearest nickel? Didn't make sense... I meant cents rounding to nearest nickle,Example: 1.22 = 1.20 or 1.23 = 1.25. ;-) "JeriSys" wrote: > What is the formula for rounding a dollar amount to the nearest nickel? Try this: For a value in A1 B1: =ROUND(A1/0.05,0)*0.05 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "JeriSys" wrote: > Didn't make sense... I meant cents rounding to nearest nickle,Example: 1.22 = > 1.20 or 1.23 = 1.25. ;-) > > "JeriSys" wrote: > >...

Click Entering Absolute Cell References into a Formula
When one is in the process entering a formula into an EXCEL spreadsheet it is possible to click on another cell to automatically have this cell reference placed into the formula. However this entry always is entered as a RELATIVE cell reference. Is there a way to have EXCEL insert an ABSOLUTE cell reference into the formula?? It seems to me that it would be so common and yet I have never been able to figure this one out. Please Help Michael Karas Hi Michael! Is this what you want? Click the cell and then press F4. F4 acts as a toggle going through the four reference options. -- ...

Compound Formula for Substitution
I need to try to create a formula to convert numbers to letters using the following scale: 0 = A 1 = B 2 = C 3 = D 4 = E 5 = F 6 = G 7 = H 8 = I 9 = J Such that 12.34 would be transformed to BCDE. This is the formula I had tried, but it won't accept: =CONCATENATE(HLOOKUP(RIGHT(ROUNDDOWN(M2/10000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/1000,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/100,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2/10,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2,1),1),A1:J2,2),HLOOKUP(RIGHT(ROUNDDOWN(M2*10,1),1),A1:J2,2)) M2 is my starting number and the...

Help with an IF Formula
Can anyone help me with a question about an IF formula? Is there any way to write an IF formula that causes multiple actions or results to occur as part of the value_if_true or the value_if_false portions of the formula. For example if an IF Formula reads: IF(A1="1",1,0)) -- the A1="1" portion is the logical_test, and 1 is entered as the value_if_true and 0 is entered as the value_if_false is there any way to make the value_if_true cause more than one result, i.e. B1=1 and C1=1. Thank you in advance to anyone who can help. Hi Ed, No. A formula can only return a value, ...

Formulas within Cell References #2
I tried to do that but it will not work in the actual cell reference I'm stumped. humejap Wrote: > Have you tried the formula > > =Concatenate("$J$",H1) > > > -- > humejap > ------------------------------------------------------------------------ > humejap's Profile > http://www.excelforum.com/member.php?action=getinfo&userid=5506 > View this thread > http://www.excelforum.com/showthread.php?threadid=39509 -- jhockstr I missed the original question, but whilst =Concatenate("$J$",H1) will join to give $J$ joined to t...

Basic example formula needed to link between sheets add/subtract
I've tired =j6+g21 but from different sheets, so its possible when i change one the other changes also. What formula do I need, because thats just not working Type the = sign, then go to the other sheet and click in J6, then hit the + sign, and go to the relevant sheet and click in G21. Hit enter. You should have a formula that looks something like =Sheet2!J6+Sheet3!G21 -- David Biddulph "gnagy84" <gnagy84@discussions.microsoft.com> wrote in message news:250E38BC-6FDF-4AE0-BB5D-EFBCA737B573@microsoft.com... > I've tired =j6+g21 but from different sheets, so i...

How do I remove the sheet name from a named formula?
I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...