Below is a formula that I am attempting to modify:
From this: =IFERROR(IF(A8<MIN(A$8:A$30)+365*5+1,(IF(AND(A8>=$E$4,A8<=$E$5),1,0)*B8*(MAX(C8,D8)/365)+B8),(IF(AND(A8>=$E$4,A8<=$E$5),1,0)*B8*(D8/365)+B8)),"")
To this: =IFERROR(IF(A8<MIN(A$8:A$30)+365*5+1,B8*(MAX(C8,D8)/365)+B8),B8*(D8/365)+B8)),"")
I am trying to do what I thought was a simple look-up. On one sheet in colum A I have some text and in colum B I have a number. On a second sheet i have a list of text that may or may not match the text on the first sheet in colum A. If the text in colum A matches one of the text items in the list, I want to have the value in colum B appear next to it in colum C. I tried to modify a vlookup formula but would only get a value error if there was a match.
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
I am working on sales information which includes postcodes. What i need to do is seperate the first or first two text characters from the rest of the postcode. I have attached a small snipet of what i am working on. Currently i am using the =Left(A4,2) but this will give me in some case a numerical value aswell. For example E1 or G1 in the case of the sample attached. Is there a formula that exists where it will just return the text values in a cell and not numerical values.
I've been assigned an assessment involving excel (2007), which involves me creating a small business system.
Part of this system involves a spreadsheet with a list of engineers and the postcode range that they are allocated to to fix callout problems.
What I would like to know, is if a callout request is made using a postcode, is it possible to automatically assign the engineer to the request?
Example, J Scott's work range is NE10 - NE19 If I entered the postcode NE12 7TD into the callout request form, is it possible to use formula or VB to assign J Scott's ID to the callout request?
All suggestions and help are greatly appreciated, I would request that solutions are kept as simple as possible as long as it's not much to ask for D:.
A possible solution i've thought of is making 2 fields in each of the engineer's rows, one being the lowest area range, and the other being the highest (e.g. J Scott's would be 10 and 19 respectivly) from there, I could possibly use formulae to assign the engineer's ID, and get his details using VLOOKUP.
Column D contains a complete address eg 60 Braugham Road, Wallasey, Merseyside CH46 1LP
I would like to be able to extract the postcode into column E, and then the address into columns F, G, H etc so that I can use the data for mailmerge docs. Is there a forumula or a macro that can be used to identify and extract the postcode? The postcode will usually be either one or two letters with one or two numbers, then a number and two letters. eg it might be as above or B6 9XF etc.
I have a worksheet of customer data which includes full postcode / zip codes. I have a separate sheet where postcodes are listed. I would like to use the postcode list worksheet to find any address matches and then create a new worksheet of the filtered addresses (to include duplicates if found). Have attached a zip file of the spreadsheet data as an example.
How can I sort the attached list both alphabetically and numerically. I have a list of UK postcode sectors which I am trying to sort alphabetically and numerically, the order should be eg AB1 0, AB10 1, G2 1, G20 1 but excel wont allow that sort order. There are three postcode formats
XX11 1 X1 1 X11 1
Where X an Y could be any letters in the alphabet and 1 could be any digit 0 to 9. Have attached a short list and formula in column B which would allow me to sort the list in column A which I can then apply to my full list.
I have a very large spread sheet of about 20,000 rows. I need to output to a cell postcode ranges based on the Zone. Not all the numbers in the postcode zones are consecutive.
For the example below, the output cell for the DWN range would display like this: 0800-0801, 0804, 0810-0813
Each output cell postal range must be specific to each zone.
Is there anyway to do this without manually doing it? I would like to tell you I've tried this and that but I've never done anything like this before so I really don't know where to start.
How would I verify a postcode format that starts with a number followed by one or two letters, space, number, letter, letter, if correct displays correct if incorrect displays incorrect
Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:
What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char
So from the above postcodes the desired results would be:
EH21 6PQ - 1 Space (8 Char) - This would be correct EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle E1 8DF - 3 Spaces (8 Char) - This would be correct LL5 1GH - 2 Spaces (8 Char) - This would be correct L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: , Groceries General Household Cleaning Education/Lessons Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
I need a cell formula (gets put in by macro) that returns part of a workbook name.
The worksheet name will always be different, but will contain either SD1, SD4, BDO, BD (not neccessarily in Caps) and it is this key code that i want returned in the cell (G2).
so if the worksheet name is
NW SD1 bobs your uncle. when macro runs G2 returns SD1 or BDO xyz. when macro runs G2 returns BDO
This IF formula does not work on the last part of this formula IF(E10>50,"$50.00",0)))) and I think there is a conflict with the IF(E9>199,(E9*0.01) but I cannot figure out what the problem is.
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
I'll try to keep this brief. Our part #'s look like this. XP014567. Where the first pair of characters stand for a value(say XP=Bolt), the second pair stand for a value(01= Grade 2), and so on. What I would like to be able to do is type in the part numer and have the description automatically populate. Is there an easy way to do this? I'm running Excel 2003.
Im using a formula to identify new part numbers. The formula is: =IF(ISNUMBER(MATCH(A217,Existing!A:A,0)),"","NEW"). However you can clearly see from the attached that if has flagged a duplicate part number as new. Why would it do that? Check out A1368 in existing and A217 in new.
I have 2 sheets, Sheet1 and Sheet2. I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change. So if i = 5, I'd like the cell to be populated with the formula =Sheet1!B5
