Let's say I have one column A (A1 TO A10) and each one of these 10 cells are filled with numbers.
Second, in cell B1 I have a random number (1 - 10).
Now, the problem is: I don't know how to make a sum of exact number of A following column cells, like number in B1.
Example: If there is number 4 in B1 than I need Excel to make sum only for first 4 cells (A1-A4).
Please what is command for this action?
I need formula to change the the date to number of months. Example : May or 1-5-2014 to be changed to days like this = 31.
View 4 Replies View RelatedI have a list of customer including customer name and customer number. Is there a way to exact customer number out only? This is the example list.
WAL-MART STORES INC - 1028
KOHL'S - 28983
JOHN'S PET PRODUCTS - 3692
PET SUPERMARKET, INC. - 2298
COSTCO WHOLESALE - INTERNATIONAL - 2783
LOBLAW CO-WESTERN GROCERS - 7424
All I want is the number behind the dash. There are few customers that have more than 2 dashes.
I have a number that I'd like to see ranked within a separate range. Ie if the number is 25 and I want to see how it would be ranked if it were in a range of:
3
43
12
19
22
7
88
(The answer would be 3 - 3rd highest). The RANK formula requires an exact match...
Sheet 1
23456
23479
23409
23512
Sheet 2
23455
23456
23457
23478
23479
23480
23408
23409
23410
23511
23512
23513
In Sheet 1 there's a Column B with data I'd like to pull using a VLOOK-UP formula. However, I need it too look-up not only the exact number match in Sheet 2, but also the cell directly above it and the cell directly below it. In the sample above if I placed the VLOOK-UP formula in Sheet 2 (at for instance the numbers 23455, 23456, 23457), I'd need it to pull data from Sheet 1, Column B (for the 23456).
Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?
Please see an example:
Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer
[Code] .....
I guess it has something to do with AVG and IF formulas.
How I can find instances of text strings containing two colon characters separated by any two (arbitrary) characters?
So in other words, "xxxx:xx:xxxx"
If I use :
Code:
SheetName.Cells.Find(What:=":**:", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
...I get a return of any strings containing two colon characters (regardless of the number of characters in-between) - because obviously the asterisk(s) denote any text whatsoever.
How do I specify a finite and exact number of characters between the colons when searching?
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies View RelatedI have text values in 2 different columns A and B. I can use the conditional formula to find and highlight duplicates but only if they are an exact match. How do I modify if they are not exact match but for example start with the same 3-4 text values?
View 1 Replies View RelatedI have 2 cells in which time is given in format hh:mm:ss , I have differentiated both whatever difference is their between them it get showed in different cell , but my cell is not showing exact time difference its showing up as ######.
View 6 Replies View Relatedi have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
here's my excel worksheets i uploaded it,
Text to Number or General.xlsx
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!
View 6 Replies View RelatedI would like to get number of cells since the cell value exceeded a given number in a given range of numbers.
example
cell address cell value
a1 1
a2 1.9
a3 -1.2
a4 4
a5 3
I would like to know number of cells since value was less than 1 : the answer is 3 (a5,a4,a3)
Number of cells since number has exceeded 3: the answer is 2 cells back ie a5,a4
how do you place 100/0 so the /0 stays static and the 100 can be used to operate? (ie: 100/00, 101/00, etc)
View 5 Replies View RelatedI am trying to compare two columns (store names) to verify if a store purchased a certain product. column a is all our stores and column c are the stores that got the product. the formula i am using is: =OR(EXACT(A2,C2:C93)) in column B and it returns a true or false value. However, when I copy the formula down column B, not only does the A value change, but so do the C values. How do I keep the c2:c93 portion of the function the changing, while the a2 goes to a3, a4, a5 etc.?
View 8 Replies View RelatedSo, it is for a hotel, I need to know to know between a Check-in and a check-out date, each day (monday, saturday) there is. In depending it is for one night or 12.. I will try to be clear: Depending on the channel of booking and the day of week we have a % of commission different. so I want to put the price in one cell and it is calculate for each date in order at the end I have the right net profit (because the right commission has been applied). Of course to complicate the commissions do not apply in the same order depending the channel and there are fixed costs which are count one time or repeat by the amount of nights. For the these things what i did seems work.
After, my boss would like to link the dates with another excel file which say for each date which "level of price" (price point) is applied and function of this Price Point we have the price applied per room type and offer
But for now, I didn't find anything what can say to me between 2 dates what dates are between...
I have percentages in Column "A" & Names in "B".
There are duplicate percentages and i need have the Names in between the percentages for 90% - 99%. Here i am using Vlook up function in Column "H" and i am getting error. This will only takes the first Name but i need all other Names for these percentages.
Hereby I am attaching the sample sheet : Lookup Values.xlsx
Have a column of cells (521 rows), on the column each cell contains a group of number
Example:
27, 539, 484, 485, 506, 594, 523, 502, 505, 525, 541, 526, 527, 529, 542, 533
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 530
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 533
Let's say I would like replace 27 to 163 but when I find and replay the "527" also changed to 5163. How can i filtered out 527 and just change the 27. I tried Match Entire Cell Content, it doesn't work.
What command do I enter so that a value in one cell is copied EXACTLY to another cell? For example, I have today's date in cell A4 and I want the exact date to be automatically copied to cell E5.
I can't seem to figure out the correct function to do this. I looked for help but there are just ways to transfer formulas over to other cells.
My date of birth is 09/08/1970.I want to know that as on today,which years,months and days I have passed?
View 3 Replies View RelatedI am using the above code to find and remove data, at present the code does not find the exact match.
For instance if someone enters as it will remove any entry with as in it.
I want it to only remove specific data, so if someone types in as, and as is not in the list to look through I wan't them to recieve a message telling them to check spelling and enter exact value of data to be removed.
IE (enter Blauv to remove Blauv, Not BL and remove Blauv, Black, Blue)
The find method has a lookat arguement which you can define as xlwhole or xlpart, by default it is set to xlpart.
If you have a cell containing Blauv
.find("Bl") will find it, .find("Bl", lookat:=xlwhole) will not.
I have one condition which needs to be matched. This code I wrote can find the first instance and then offset data to another worksheet accordingly.
My question is whether than can be done for every other instance that appears on my sourcesheet 'Invoicing'. I have considered putting a loop in, but this will only find the first instance over and over again which is pointless.
i have a set of words in sheet1 column A, in sheet2 columnA, i copied the words and type the meaning on columnB. in sheet1, i have a command button adjacent to the word in columnA, when i click that button, it will go to sheet2 with the same word beside the button in sheet1 columnA, so that the user may see the meaning of the word in sheet2. is it possible to generate a code like that?
View 9 Replies View RelatedIs it possible to use HLOOKUP without exact matches. e.g. < =HLOOKUP(E1,Actuals,2) > where E1 is "2009 October" and the lookup value in named range "Actuals" would be simply "Oct".
View 4 Replies View RelatedIn one sheet I have customer_code in one sheet (Customer_code) and this is multiple like
C00002604,C00002604,C00002604 and in another sheet (Customer_code_details)
I have the same code but here the count us much more than earlier sheet (Customer_code). if Customer code exists the same code 3 times the same code exists also in sheet (Customer_code_details) for 5 times.
In customer_code sheet I have 8724 data but Customer_code_details sheet I have 8840 data.
I am normally sorted the two columns and checking t manually.
Is there any way to do vlookup with count?
I have a series of numbers in cells A1 to A5, e.g: 5,15,19,3,105 now my target value is 23. Is this possible that we can come to know which values we can sum to get our target value. in this case cells sum of A1,A2,A4 is 23 which is my target value.
in the same way i will set other target value , it should show which are the number which should be summed to get my target value.
I have written a basic search which pastes the results on to another sheet.It works, but i have a small issue with it.If I search for "A" it will give me all the results for "A" but it will also give me any result with a combination with "A" in it, eg. BA or BBA etc I need to have results based on the exact search. What do I need to do to make my search result and exact result?
This is currently what i have
Sub Rating()
'
' Rating Macro
'
Dim Wrd As String[code]......
I am writing a macro that will take data from a series of objects and then filter 3 columns according to their attributes.
EX.
Object 1 Example1 20140306 10
This combination of values is unique so is used to grab the value in the third column after the filter has ran.
My issue is that in the second column the actual values are 50201403069999 and they change per value although they represent the same date "20140306" so manually I can type in "20140306" and the filter will show the results however it seems the macro requests the exact which I can't provide as this the superfluous numbers change.
Is there a way to make it the auto filter search the value??
My second issue is how to extract the value sought once found. Since this cell will be different each time, what is the best way of selecting would it be C3 select then down?
Here is my current code:
Code:
Sub IMPORTPRICES()
Dim i As Integer
For Each Ativo In Sheet4.Range("A2:A6")
[Code].....
I have a sticky problem. I have a spreadsheet set up that counts almost everything I do at work, and I would like to input the exact time I receive a call and have the spreadsheet break it down into time periods, e.g., 8-10, 10-12, 12-2, etc., to count what time of day I get most of my calls and sales. In other words, I want the sheet to read the time I input and count it as part of a specific time period, and then count the number of calls taken and sales made from those calls. I can easily compute my closing percentage when I get those numbers. Also, since I have all the days of the month on the same spreadsheet, would it be possible to break them down by day (range)?
I can see where knowing how to do this could come in handy for other projects as well.
no VBA. I play with it at home, but I also struggle with it mightily.