I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...).
I try to write the function that gets some "ref" and returns appropriate item. For example: if I give to function "C3", it returns "AAA" if I give to function "R18", it returns "BBB" (cause it between R15 to R26) if I give to function "R9", it returns "BBB" also.
I need a formula which I am using to count instance of sickness. I include a sample copy of my data which I hope will make my request clearer! I'm currently using the below formula to count the number of continuous instances of any code starting with "SICK" =(SUMPRODUCT((LEFT(C$10:C$37,4)="SICK")*(LEFT(C$10:C$37,4)<>(LEFT(C$10:C$37,4)))))
I would also like this formula to count any code starting with LTS as well (Long Term Sick). Due to the nature of sickness there may be continuous periods where both SICK and LTS codes are used, at the moment I can not come up with a formula to return the value I need. These columns may contain other codes but for simplicities sake I have only shown the relavent ones.
I'm trying to create an Excel function that allows me to choose a row from a table based on text found within entered data. For example, say I have a table called "categories" that looks like this:
Categories cat dog mouse
Now in a different section of the workbook, I want to be able to enter an arbitrary number of sentences, one per row, that look something like: "It's raining cats." "The mouse is hiding."....................
The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:- 1 class1 class2 class3 class4 Gg Hi Fr Sp 2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE 3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
I am trying to check whether a cell is part of a named range. I have a 4*2 range named "kompleks" and wants to check whether the selected cell i part of that named range. I've searched google and this forum, but nothing will work. I've tried
If Target.Name = Range("kompleks") Then If Target.Name = kompleks Then
And also
If Target.Name.Name = Range("kompleks") Then
It is used as a private sub for the worksheet_change.
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
I have below four column range. I need a formula to sumproduct column A and column D, where column B = "n1", column C = "xyz" and until sum of column A reaches first largest value which is less or equal to a variable, say 15. So, the rows would be 1st, 2nd and 5th. And the result - 1,440.
A B C D 2n1xyz110 5n1xyz112 8n2abc112 3n1abc111 6n1xyz110 6n1abc114 3n1xyz114 2n1abc112 3n2xyz114 8n1xyz114 8n1xyz115 4n1abc115
I have worked our an CSE formula below, but it is really massive. Need to have much simplier one.
I am looking to make pictures a named range to be used in a drop down box. I insert the pictures on sheet 2 and name the cell range they are in, but the drop down box on sheet 1 is blank??????
i have a merged cell which runs across a multiple columns (but a single row). i would like to insert a column in the middle of this merged cell, but whenever i click on the column header to insert a column, the entire merged range is selected.
is there a way to change the excel setting to avoid this?
Trying to assign code to comm. button on User form to copy lets say:
(sheet1, rangeB2:B21) to (sheet2, first blank row rangeB2:B21) and paste it as text value one more question: what to be aware in case of sheets format (merged cells, hidden rows...)
I have a named range TOT_BUDGET. We use the current month number to access this range's index for current budgets.
=INDEX(TOT_BUDGET,3)
My quandary is that I need to calculate the YTD budget for one of our dashboards. So, I need to get months 1, 2, and 3 and SUM them. Next month I'll need to get 4 months, etc...
I've looked at SUMIF, but it needs to match against some sort of criteria... I don't have any numeric month labels on the budgets page. I cannot figure out what range/array I would match the month number to.
Now, I know the easy way would be to put a month number at the head of each column, reference that array and do the SUMIF. Blech.
Ideally, I would love to just be able to say =SUM(INDEX(TOT_BUDGET, 1-3)). Is there an Excel function that will let me select an array slice?
------------------------------
In addition, we are getting the Total Sales number out of Great Plains via an SQL query. We summarize many accounts using a pivot table. We then access this table by using the GETPIVOTDATA function.
I have ComboBox on a UserForm that is looking to a long list on a worksheet. A lot of the entries in this list start with a brand name instead of a more discriptive name. I really need to be able to find an entry with any key word (not just the first word) in this list.
I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).
I have been tasked with creating a scheduling system for my work. There are three groups of shifts that can be selected, Opens, Swings, and Closes. I have been asked to make the cells turn red if a user selects a sequence of shifts. We are trying to avoid having a person working three scenarios:
Close>Swing>Open Close>Off>Open Close>Open
If they select the shift that meet this scenario, then I need the cells to turn red. The complete list of shifts are on the "Data" tab and the the three scenarios have been inputted into the "Jan" tab in E13:G15.
I am using the code below to change the background of the cells in my worksheet based on what is in the cell. How can I modify it to accomplish the changes below and meet the criteria above?
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I have a sheet where i have many differently named areas (like state1_1 and state1_2) When I doubleclick on a cell then a macro should run with following criteria: 1) Macro will run if the doubleclicked cell is part of any range in the list. Here I mean that names of ranges which belong to that list start with word state (like state1_1 and state1_2). No other ranges should not be in that list. If the cell is not in the range that is part of the list, then nothing should happen.
I have the following formula that is supposed to grab a value from C2 and check for that value in a range of cells and if it matches it is supposed to display the corresponding value in another range of cells. What am I missing??
I am trying to Lookup a country to see if it is classified as a "Developed Country".
My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging.
I have tried an IF statement using the Match function and it does not work.
=IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
