I am trying to get a formula to work where it will search a single cell (on a sheet called "Calls") to see if a particular code is in there, if so return a value using HLOOKUP from a table on a sheet called "Values". The problem I am having is how to use the wild card etc because everything I have tried returns an error...
The code I am trying (Without wild cards, because they all failed) is...
Code: ....
I built an Excel Macro to pull information from my schedule and compile it within another Worksheet. The Macro below searches for an instance of "Unavailable" and then populates my Data Worksheet with the WeekDays (ie: M-W-F or ALL Week), the Details (ie: Vacation or Training) and the Name of the employee. I struggled with a way to populate my employee name as "unavailable" isn't always offset by the same amount of characters to employee name (as it is with WeekDays and Details). Instead, I collected the row in which "Unavailable" was found.
I used the Vlookup function to populate the number of Absences and it worked swell.
=VLOOKUP(C2,O:P,2,FALSE)
C2 is populated with Weekdays (ie: ALL week or M-W-F)
O contains a list of possible day combinations
P contains the number of days
I hoped to use a similar function to populate my employee names. This didn't work well a 'tall.
=VLOOKUP(A2,L:M,2,FALSE)....................
I am creating a Spreadsheet to work with a game which I am designing. I want to create a combat calculator which will generate formulas based on which class a person chooses to be, and their choice of weapons, armour and other equipment (makes things a lot easier for me to visualize). I have created the first worksheet which will act as the interface where users can input their character configurations and a second worksheet where all of the data is stored, included items and their values.
Assuming that on the interface I have cell A1 which will let the user select their class (Warrior, Archer, Sorcerer).
I also have cells B1 = Helmet, B2 = Body Armour, B3 = Leg Armour, B4 = Feet Armour, B5 = Hand Armour.
I would like cells B1:B5 to create a Data Validation based on the class which is selected in A1.
E.g.
IF A1="Warrior"; B1 = A list of Melee Helmets, B2 = A list of Melee Body Armour... etc
IF A1="Sorcerer"; B3 = A list of Magic Leg Armour...
I have managed to do this with the following formula, but only if the data is stored in the same sheet.
Cell B1
=IF(A1="Warrior", =meleeArmour1, =IF(A1="Archer", =rangeArmour1, =magicArmour1))
Cell B2
=IF(A1="Warrior", =meleeArmour2, =IF(A1="Archer", =rangeArmour2, =magicArmour2))
[NOTE]
=meleeArmour1 represents the range of cells where the Melee Helmets/Head Armour is stored.
=magicArmour2 represents the range of cells where the Magic Body Armour is stored.
The group cell names do not seem to work if they are on a different sheet, nor does it work if I replace the group cell name (=meleeArmour1) with the full formula (=Sheet2!A$1$:A$10$)
I have a code which can search a column of data and register if any of the cells are equal to eg. "LOMM" or another given shortname.
But sometimes the cell that i want to compare to "LOMM" contains multiple shortnames. (eg. "LOMM, GIV, ANDHE").
In such a case I cannot use the if cells(i, y) = "LOMM" then...
I need an "if"-statement which can identify LOMM in a cell containing multiple shortnames.
I have tried using the FIND and SPLIT functions but it does not work.
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
to search for specific text in a cell but not just one word or letter, in fact it could be many. I need to first verify that the exact texts exist in a table and then match the various text instances with corresponding data in that table.
At the moment, this works to search for one type of text and verify it's exsistence in the able and indeed return the corresponding value, but i need it to do it for all verified texts
=(INDEX(A2:A4,MATCH( LOOKUP(9.99999999999999E+307,SEARCH(" "&$B$2:$B$6&" "," "&D2&" "),$B$2:$B$6),B2:B4,0),1))
Example attached
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedSee the attached workbook.
Please edit the formula in column B so that it only gives a BINGO if the adjacent cell in column G is less than 51.
So cell M16 should not be a BINGO because cell G16 is not less than 51.
Attached Files : Book1.xlsx
I am trying to add a number to a cell for work breaks. Below is what I am trying to do:
If F3 is < 4 then 0
If F3 is > 4 but < 6 then .25
If F3 is < 6 then .50
I have a VLOOKUP formula, so when the cell above (C5) has a value, it returns the value from a defined list. Simple so far and it works great when I manually input the value in the cell (C5).
The problem occurs when I change C5 to contain an AVERAGE formula? Any ideas why it will only work when there is a plain old value in there, but not a formula?
This is the VLOOKUP formula I'm using:
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
I have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.
I came across a userform which was perfect for my needs, as I only had to create additional fields, which I did and have attached. However, since I have more than 10 fields, I was getting an error with respect to the listbox function, where it stated that the list property could not be found. I gathered from the searches I did, that this is the max limit for this particular function. I have been unsuccessful in finding a solution, partly because I don't know very much and I'm not understanding how to incorporate some of these solutions into the vba code. I've created simple userforms before, but this one is a little more advanced than my current capabilities.
View 2 Replies View RelatedI've been working on a database for a Dutch nursing home, but I'm struggling to get it to work. The file provides for a userform that enables users to search for residents and retrieve their appartmentnumber and the adress of their legal representative.
This userform consists of three parts; in the first, the user can type the full or partial name of the resident of interest in a textbox; in the second phase, all matching registrations are presented in a listbox; and in the third phase, a macro searches for the name selected in the listbox and retrieves the corresponding appartmentnumber and adress.
The first two phases of the userform work fine, but in the third phase, the macro fails to find any matches even though it is practically the same as the macro used in phase 1.
I am creating a spreadsheet for inventory use. I want to have a running total in (1) cell based upon a new/different number being entered into a different cell.
Column B, Row 1 (This will be a new/different number entered every day - inventory in or out, so positive or negative number) Column D, Row 1 (This will be a running total based on numbers inserted in previous 2 columns)
Basically what I have is a key inventory. So there is column A with key number, B should be keys IN, C should be keys OUT, or ideally B would be IN and OUT meaning, for inventory IN input a positive number and for inventory OUT input a negative number, and D Should be total.
Column E represents number of keys currently in the inventory. I was going to hide the current inventory column so all you see is IN/OUT and total. So what I need is to be able to just come in and type in a number in the IN and/or OUT column, without having to add/subtract it with the number already in that column.
Bottom line, I'd like to be able to use IN and/or OUT columns to just type in numbers as they come and not have to worry about what's already in those columns and get correct total number.
To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.
View 4 Replies View Relatedformula to separate information in a single cell into multiple columns. I have hundreds of rows of data, so a formula to quickly separate the information into individual columns would be good. Below are a few lines of data.
ALMADEN STATION* 900 227 168 182 176
OAKRIDGE STATION* 900 276 332 362 349
OHLONE‐CHYNOWETH STATION* 900 469 480 522 504
ALUM ROCK STATION 901 947 925 1007 972
BAYPOINTE STATION 901 181 196 213 205
BONAVENTURA STATION 901 137 140 152 147
CAPITOL STATION 901 650 680 741 715
CONVENTION CENTER STATION 901 788 814 886 856
CURTNER STATION 901 569 571 621 600
ORCHARD STATION 901 87 94 91
i have a formula that reads off a single cell at the moment.
=IF(N38="MB","KL","MB") this works fine. I need the this to read off a range of cells from N38 to N43.
I have a spreadsheet (called output) with a single list of unique account numbers in column A. In columns B, C, D (column B = Invoice, column C = Sales, column D = class), I need to add information from a data file based upon certain search criteria which is as follows:
For each account number I need to list an invoice number and the corresponding sales amount and class. I need to list the invoice with the highest sales amount. There is only one catch to the issue I am encountering and this is I need to first look for the highest amount in Class A sales, if no Class A sales, then look for the highest in Class B sales.and then Class C.and then Class D. If there are no sales in Class A, B, C, or D, then return a value of no match found.
I'm looking for a way for Excel to combine formulas together into a more compact form for me.
Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.
Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?
Here is the one-cell version of a formula I work with.
[Code] .......
That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.
Attached File : Excel Formula Combination Question.xlsx
In my business I deal with a ton of different dimensions for my products. A lot of times the dimension will be within the cell as follows, "Product A 17x27 Brown", is in a single cell.
Is it even possible to write a code or formula that will find the "17x27" in the description, recognize it as a "dimension" and then run the formula that I need which is
I need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.
Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx
1.xlsx
2.xlsx
I imported the data from someone. And the cell in the column comes with the single quote'infront of the cell value. For example, the value that you see in the cell is "920-F-Y", but if Click on that cell, it turns out to be '920-F-Y, there's a single quote infront of it. I can't do a replace all on that single quote to empty space for some reason. Might just be my excel, or there's just something wrong. The problem is, I have 2 sources of data. I imported the first one, it has the single quote infront of it, i imported the second one, it has no single quote infront of it. I want to do a comparison, but I can't because it's different by single quote.
You can open my attachment, and try to replace that ' (single quote) with a space, and excel will complaint that it can't find it. I could go through each one and delete it individually, but consider that there are 5000 cells, it's just a lot of work.
I get new worksheets on a regular basis which are of the same format (same columns with headers) and I have to perform both vlookups and sumifs on them. Their are about 12 different tabs I perform the vlookup on based a vendor name in A2 and its corresponding tab. I am tired of typing the same thing over and over again.
What I'd like to write is a Control Panel with all the equations so I can just copy/paste them in when I get a new sheet.
How can I write a vlookup which would search for the value which is one cell to the right of where I put the formula in?
Similar to:
=vlookup(current cell over one, 'vendor1sheet'!A:C,3,0).
I would then want to copy it down the worksheet. Currently type in column E everytime I get a new sheet:
=VLOOKUP(F2,'Vendor1'!A:C,3,0)
The catch
Column A has the vendor name, there are about 12 vendors, each with a different tab with related data which I am searching through.
Ultimately, I'd like to have an equation that would do the vlookup (and sumif) as described above and also use the vendor name in column A to look on a particular tab.
Something like:
=vlookup(one cell to the right,'VendorTabFromVendorNameInColumnA'!A:C,3,0)
I was going to use nested if statements on a control panel sheet with a column for the vendor name and a column for the vlookupformula and than paste a single formula all the way down column E:
In column E, every row:
If(A2=vendor1, 'controlpanel'!vendor1equation), if(A2=vendor2, 'controlpanel'!vendor2equation), etc.
Again, the vlookups would all check for one cell to the right of where this nested if statement was (located in column E, looking in F).
I am looking for a formula for a cell that will do the following:
from :
robert johnson hot dog
To this
robert+johnson+hot+dog
I would like it only to change the space between the words to a + sign.
I've got a long list of company names, i'm trying to create a formula to see if they match but the problem is the names won't match exactly (e.g it would be Joe Bloggs Limited & Joe blogg's ltd). I've thought about it and think the best way to see if they match would be to take the first word from the first cell (in this case Joe) and then search for it in the next cell and return TRUE if it exists anywhere in the cell and FALSE if not.
View 3 Replies View RelatedAttached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.
I would like to be able to get a list of all values in a given column that match a specific name in another column.
Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.
For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.
The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.
CPT Sample Book.xlsx
I have this worksheet, that was created by some one else, that has formulas all over the place. What I want to do is click a button and have all the cells that contain a formula to change the font color to red and fill the cell with a certain color.
I am going to create a userform with one command button. I know the code will go in the command button.
I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"