Or Exact = True
Nov 19, 2008
I 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
ADVERTISEMENT
Aug 2, 2009
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
View 8 Replies
View Related
Nov 15, 2006
I am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.
View 2 Replies
View Related
Jun 4, 2014
So, 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...
View 2 Replies
View Related
Jul 11, 2014
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‎
View 6 Replies
View Related
Jun 4, 2014
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.
View 14 Replies
View Related
May 18, 2009
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.
View 5 Replies
View Related
Feb 19, 2010
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 Related
May 27, 2014
I 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.
View 3 Replies
View Related
Feb 17, 2009
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.
View 4 Replies
View Related
Dec 1, 2009
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 Related
Oct 27, 2009
Is 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 Related
Nov 12, 2012
In 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?
View 3 Replies
View Related
Mar 11, 2013
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.
View 4 Replies
View Related
Jun 17, 2013
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]......
View 1 Replies
View Related
Mar 6, 2014
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].....
View 1 Replies
View Related
May 12, 2014
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 Related
May 29, 2007
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?
View 9 Replies
View Related
Oct 25, 2007
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.
View 9 Replies
View Related
Mar 3, 2010
All those tabs have same format. Anyone know if there's a quicker way to do it? Each tab has different names. Thanks for your help.
View 9 Replies
View Related
Jun 1, 2006
I can record a macro that will sum a column of data at the end of data in that column - simple; my problem is getting it to work on other workbooks that have a different amount of rows in it. I tried recording the macro using the relative button as well as without using it, but either way I do it, I can never get it to work on a different workbook. For example, I record the macro, then remove a couple rows after to simulate running on another workbook, and then when I run it, the sum gets put in the cell that used to be the end of the column instead of the real end of the column…Same is true if I add some rows. I don't know if I am using the right terminology below (OK, I know I'm not) but it gets the message across best when I say I think I need something that will do the following (substitute "code" for "make believe code"):
1. Go To cell F1
2. Go To last entry In this column
3. Move down one cell
4. Sum F2:Fx (where x = one cell above current selection - the last populated cell In the column With data In it before the sum)
Instead of this (what I have right now)................
View 6 Replies
View Related
May 8, 2007
I need to compare a particular range of cells using Exact and display the result of comparison of ( Sheet1 and Sheet2 ) in Results screen. It can be done by statically putting up range of values but Issue comes when user dynamically input the range of values
Dim FirstCell, LastCell As Range
Set FirstCell = Application.InputBox("Click the Last cell to end comparing to", _
Type:=8)
Set LastCell = Application.InputBox("Click the Last cell to end comparing to", _
Type:=8)
'Statically specifying the range - Works fine
Worksheets("Results").Range("A1:D4").Formula = "=EXACT(Sheet1!A1,Sheet2!A1)"
'User Inputs the range - Does Not work
Worksheets("Results").Range(LeftCell, RightCell).Formula = "=EXACT(Sheet1!FirstCell ,Sheet2!FirstCell )"
View 8 Replies
View Related
Aug 8, 2007
I looked for this - but no luck and the tips did not answer it either
(in fact I have used an example but it does not work - in my case ).
I am using H lookup but need to find the exact match.
I know that you can use false on a Vlookup to get an exact match but it does not appear to be working with Hlookup.
I attach an example and hope someone can enlighten me...
View 4 Replies
View Related
Dec 16, 2007
I am try to convert a letternumber (both single) to a number, ie 3b to 5, 7a to 17 etc. I am using a vlookup formula at the moment, =VLOOKUP(J3,'Test levels'!$R$4:$S$54,2) but if J3 is 4b it finds 3b and puts in that reference.
View 3 Replies
View Related
Mar 3, 2008
I am trying to use the advanced filter with a not equal to operator, where the requirement is that the output not match an exact text string. I have tried <> and <>"???" (where ??? is the text string) and neither filter is working.
View 2 Replies
View Related
Jan 27, 2014
I 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.
View 8 Replies
View Related
Mar 6, 2014
When an exact match to the lookup value is not found, the function goes to the highest value that is less than the lookup value.
What do I do if I want to return the closest value, but not less than the lookup value?
For exampe, in my screenshot, if my lookup value is 6 horsepower, I want to return the efficiency for the next highest value (if no exact match) for 7.5 HP. Function would return 89.5
VLOOKUP.jpg
View 12 Replies
View Related
Apr 7, 2014
For example Cell A1 is 9.5 x 2.5 which gives 23.75
but what is actually displayed to users is rounded to 24
So how do I use 24 as a reference instead of 23.75?
So in Cell B1, it would be =A1x100 but the value is 2400 instead of 2375?
You can't use the ROUND function because if users decide to use 23.8 then the B1 cell needs to be 23.8x100
View 2 Replies
View Related
Jan 16, 2007
When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.
In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.
View 9 Replies
View Related
Mar 2, 2009
Okay, so I have this project for my accounting computer applications class. I'm making it so much arder on myself than what it probably should be. But anyways, this project is a four year plan spreadsheet for the business/accounting division of the school. Inside the spreadsheet is a page that has 15 different tables, representing 5 years with 3 semesters each, fall spring and summer. On the requirements for the major chosen, I would like it to automatically recognize if the class has been placed in the semesters tables and recognize all of the information needed. The only problem is, a single vlookup only allows one table per cell but I need all 15.
I've heard of using the if statement along with these but I've only heard of it being done with a few tables and not 15, and I don't understand how exactly that works anyways.
View 7 Replies
View Related
