Wildcard Incorrect

Dec 7, 2007

I have tried everything I can think of to get this to work.

I am searching column B for anything containing "Tease", then if found, look to see if column J contains a "W".

Excel doesn't report an error, but this doesn't work:

=SUMPRODUCT((B3:B40="*Tease*")*(J3:J40="W"))

View 10 Replies


ADVERTISEMENT

IF, AND, OR - Returns Incorrect Value

Apr 29, 2009

I am having problems with the following formular in that it does not return what I am expecting.

I have 2 columns of data that hold Y/N values and I would like either "Yes" or "No" to be displayed in Col C for the 4 possible combinations that the interaction of Y and N in columns A&B give.

The data is
Col A:A = Y or N
Col B:B =Y or N

The four possible combinations and outcomes are:
Col A = Y & Col B = Y outcome in Col C = "Yes"
Col A = N & Col B = Y outcome in Col C = "Yes"

Col A = N & Col B = N outcome in Col C = "No"
Col A = Y & Col B = N outcome in Col C = "No"

The formular I have tried is:
=IF(OR(AND(A1="Y",B1="Y"),A1="N",B1="Y"),"Yes","No")

However while three of the outcomes for the combinations are correct (N/Y, Y/N, Y/Y), the output for the N/N combination is incorrect and returns "Yes" instead of "No".

Just wondering if anyone has any ideas as to what is wrong with this?

View 9 Replies View Related

VLOOKUP Returning Incorrect Value

Nov 28, 2012

I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.

My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.

View 4 Replies View Related

Deleting Incorrect Cell Name?

Jun 24, 2014

I have a lot of calculations going on so I thought I would name some of the cells where factors come from to make it easier to follow.

I made a typo and named a cell wrong, is there a way to delete it? Right now I have a correct and incorrect name attached to the cell.

View 3 Replies View Related

Save Path Is Incorrect

May 27, 2009

I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?

Sub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet

If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New sheets will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub

With Application
.ScreenUpdating = False

On Error GoTo ErrCatcher
Sheets(Array("Copy Me", "Copy Me2")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues....................

View 2 Replies View Related

Incorrect Scoping Of Variable?

Mar 1, 2012

I have two pieces of code in module 1 and module 2 respectively:

This code is meant to find the first row number that has a string value of "" in column A:

HTML Code:
Sub Macro1()
Dim cell As Range
Dim r As Double
Dim p As String
For Each cell In Worksheets("stock in").Range("stockcode")
p = cell.Value
r = cell.Row
If p = "" Then Exit For Else
Next cell
End Sub

BTW column A will contain formulas that evaluate to "" so I assume cell.value will = "" even if it contains a formula?

In the second module a sort is executed on the range based on the number of rows:

HTML Code:
Sub Macro2()
'
' Macro2 Macro
'
Call Macro1

ActiveWorkbook.Worksheets("Stock in").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stock in").Sort.SortFields.Add Key:=Range("A4:A" & r) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

[Code] .......

My code is falling down because I think my variables arent scoped right or my code is in the wrong place. Also macro1 will eventually be executed from a button on a different sheet (I dont know if this will matter).

The whole point of this code is to get around the fact that if you perform A - Z sorts on cells that contain formulas those that evaluate to "" will be at the top leaving lots of blank rows before actual visible data.

View 9 Replies View Related

Incorrect Result Of IF Statement

Jun 27, 2012

I have a line of code that compares cell values with a variable.

Code:
dim rmycell as range
If rmycell.value = MyTarget then

This works fine when rmycell.value is empty or has a value. However, sometimes rmycell has a #VALUE! Error. When debugging the code I can see this error showing rymcell with a value of "Error 2015". The MyTarget variable is "abcdefg", so the If statement should resolve as

Code:
If rmycell.value = MyTarget then
becomes
Code:
If Error 2015 = "Abcdefg" then

Which should be a negative result. Except it isn't. The code that is conditional on a positive result runs whenever there is a #VALUE error, just as if the 2 values were equal.

View 3 Replies View Related

VBA Function - Incorrect Syntax

Aug 22, 2007

I'm trying to create a new function in Excel and for some reason the syntax is incorrect and it won't let me use it in the sheet. I do not know what I'm doing wrong so any help would be appreciated. This function will allow me to interpolate the term structure of interest rates between dates (I think) My code is

Function INTSPOT(spots, year)
'Interpolates spot rates to year
Dim i As Integer, spotnum As Integer
spotnum = spots.Rows.Count
If Application.WorksheetFunction.Count(spots) = 1 Then
'Single rate given
INTSPOT = spots
Else 'Term structure given
If year = spots(spotnum, 1) Then
INTSPOT = spots(spotnum, 2)
Else
Do
i = i + 1
Loop Until spots(i, 1) > year
INTSPOT = spots(i - 1, 2) + (spots(i, 2) - spots(i - 1, 2)) * _
(year - spots(i - 1, 1)) / _
(spots(i, 1) - spots(i - 1, 1))
End If
End If

End Function

View 9 Replies View Related

Identifying Incorrect Data.

Feb 10, 2009

In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.

I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.

Example1 H2 shows 6 11, therefore cell G2 should show 11.

Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11

Sheet1 HIJKL1ContainerID26 11135 8 11245 7 11355 7565 6 9 11675 6 9 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Incorrect 'Last Cell' In Worksheet

Aug 26, 2006

CTRL/END key combination should goto and select the last cell in a Worksheet. So for a Worksheet using Rows 1-4135 and Columns A-Z it should indicate Z4135 as the last cell
My current worksheet uses these Rows and Columns, but CTRL/END indicates last cell as being AB4506. (Inflated by 371 Rows and 2 Columns)

Deleting the unwanted Rows or Columns, or "Clearing contents" of them does not affect the result given by CTRL/END which still shows AB4506.

How can I adjust/correct the last cell indication?

Usual work-around is to select the wanted portion of the worksheet and copy it to a new blank worksheet, but is there an easier way?

View 5 Replies View Related

Incorrect Optionbutton Value On Initialize

Oct 3, 2006

I have a userform on which there is a frame containing 8 option boxes. After a query, the results are displayed in the form. If I have a value of 1 in a cell, optionbox1 is checked; a value of 2 checks optionbox2, etc. It works great EXCEPT when first initialized. At that point, it checks the last optionbox, even if the number is 1. I have built a next and previous feature to scan the data, and when I return to the first entry, the correct box is checked. I tried coding blanks into the fields prior to populating them, but I still get the same results. Is there some explanation available so that I may remedy this? I'd really like the first piece of data to be correct.

View 2 Replies View Related

The Text Changed To Incorrect Value.

Mar 13, 2007

I have a text file which have a column with data like xxx . however, when I open the file with excel and it displayed incorrect value like xx.xxx, x.xxx

I tried to change the format cell to text, but it still cannot display original data...

View 9 Replies View Related

Incorrect Username For Workbook In Use

May 14, 2008

Usually when you try to open a file that someone else is using, it lets you know the file is in use, and lets you know who is using it. However, the situation I have is that when a user tries to open the file that is in use, it tells them that the file is in use, but gives them the wrong user who is using it!

For example, User1 opens the file and closes it again. User2 comes along and opens the file and stays in the file. User3 comes along and tries to open the file, but instead of saying User2 is using the file. It says User1 is using!

The file we have was originally built in Excel 97, but we using it through compatibility mode in Office 2007. The file is needed by various users, but putting it on shared access is not an option.

View 9 Replies View Related

Index / Array Results Incorrect

Jun 5, 2014

If a player does not qualify, their name and score should not be included in the final results.

I currently have their Name missing, but the NUMBER is showing up.

I've explained more in the attachment.

INDEXARRAY.xlsx

View 3 Replies View Related

Display If Hyperlink Path Incorrect

Jun 9, 2014

I have a query around a hyperlink.

Is there a function that i can add to a cell to display if the hyperlink path is valid

i.e., to show that the hyperlink path does not exist, I am feeding the hyperlink from 2 cells ...

View 1 Replies View Related

Correct/Incorrect If Cell Contains Letter

Jun 12, 2009

I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:

- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";

or

- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".

Basically the 8 is always correct with an R and the 7 with an L: ...

View 6 Replies View Related

VLOOKUP Returns A Incorrect Match

Aug 30, 2007

In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way.

View 14 Replies View Related

Can't Multiply...why Is Simple Multiplication Incorrect?

Nov 7, 2008

On the PROPOSAL tab, at I41, a simple multiplication instruction is wrong. It's multiplying 72*186.53 which = 13430.16 in the real world, but on this sheet 11 cents are missing. I'm pulling my hair out on this one....

View 4 Replies View Related

VLookup Function - Incorrect Results

Nov 10, 2011

I have a problem with VLOOKUP function and I am looking for a workaround.

SheetA I have a column of business names and another column of the category.

SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...

View 7 Replies View Related

Pie Chart Percentage Calculation Incorrect

Aug 21, 2013

This is so simple, yet Excel doesn't give the correct value. So, here you go, I only have 2 Cells and 2 Labels. One Label for one Number and the other Label for the other Number.

For the Pie Chart I chose Cells:

Cell B36 with the number $54288
Cell B39 with the number $166113

By simply viewing this one can see one of the Percentages should be around 33% and the other 66%. However, Excel gives it a Percentage of 25% and 75%.

How can I get the correct values?

View 2 Replies View Related

Incorrect Syntax For Setting Value Of Variable

Nov 26, 2013

I'm not sure where my syntax is wrong, and I can't think of what to google to learn more about the possible options I have when creating and declaring the value of variables.

I am simply trying to define the variable aWB as a workbook, whose name is found in cell B6 on the worksheet named "Start" in the workbook named "DW1". This seems to make sense to me, but I am not typing it correctly.

Code:
Dim aWB As Workbook
Set aWB = Workbooks("DW1.xlsm").Worksheets("Start").Cells("B6").Value

View 9 Replies View Related

If Statement Providing Incorrect Answer?

May 20, 2014

In cell K8 is the value (General) 800. In C8 is the value (Also general format) 768.

=IF(C8

View 4 Replies View Related

Incorrect Total For Summed Hours

Feb 10, 2009

I am stumped on a formula answer I am getting on a simple timesheet. The timesheet is set up with an In and Out column for each day and person for the entire month. A formula calculates their total hours worked on a daily basis, minus their lunch (30 minutes per day). So far, so good.

The trouble comes when I try to sum the hours worked for the entire month. I am getting an incorrect total. The example I am working I am summing E3:BN3, which show the employee working 15 days in a month, 12 net hours per day. 15x12=180 hours a month. My sum total is showing up as 300:00:00. It may help to know we use a 24 hour format. I have the results cell formatted as [h]:mm.

View 9 Replies View Related

Syntax Must Be Incorrect For Range Reference

Feb 13, 2010

The following sub to create and name wsheets results naming the new sheet with the value of "A9" only, but what I want to name the new sheet is "A9" + "B9".
Sub CreateWorksheets()
Dim newSheet As Worksheet, itemSheet As Worksheet
Dim cell As Object
Dim itemrange As String

Set itemSheet = Sheets("BIDFORM")

Application.ScreenUpdating = False
itemrange = "A9:B9:" & itemSheet.Range("A9").End(xlDown).Address
For Each cell In itemSheet.Range(itemrange)
If SheetExists(cell.Value) = False Then
Sheets.Add Before:=Sheets("BACK SHEET"), _
Type:="C:PathFile"
Set newSheet = ActiveShee
newSheet.Name = cell.Value
End If
Next cell
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Sum Formula Returns Incorrect Result

Dec 1, 2009

I have created a table in excel, and it has a very simple sum totals. When i include all cells in the table the total becomes 0. When I exclude a cell in the table, the total shows. I have deleted the row and re inserted another one and copied the cell content from above that was not having a problem,

View 9 Replies View Related

VB: Incorrect Syntax For Assigning Variables

Jun 3, 2006

I know that I cannot automatically assign UniqueCount the value of that formula. How would I format the line such that I can indeed assign UniqueCount the row value of that line?

View 9 Replies View Related

Incorrect Percentage On Pivot Chart

Oct 20, 2007

If I simply publish the pivot chart, the correct percent values are displayed on the y-axis. However, when I publish it with the interactivty option, the percent values are wrong. For example, the actual value is supposed to be 13.7% but graph shows 46496000.00%. The other oddity is that the table published in interactive mode has the correct values.

View 3 Replies View Related

Date Formats Incorrect When VBA Opens Workbook

Apr 29, 2008

I'm new to VBA so my i'm having alot of problems figuring out simple stuff.

Below is my script and when i run it, the dates turns out incorrect.

I noticed this only happens to dates that are before 13th of each month.

Example,

1st May 2008 ( 01/05/2008)

will turn up as

5th Jan 2008 ( 05/01/2008)

However when i manually open the file Todays trades.csv
The date looks just fine.

Is there some problem with using VBA to call up the file?

View 9 Replies View Related

Serial Number Date Displaying Incorrect Day?

Dec 16, 2008

I maintain a class register in Excel to monitor student attendance. The first row shows the date of the class in the form dd-mm.

I need to identify all dates which fall on a Monday and thought that if I custom formatted a new row as "dddd" and enter the formula =DAY(cell ref) into the cells of this new row it would achieve this- I could easily spot the Mondays for the period under review.

What I'm finding, however, is that the formula seems to incorrectly state that 16th September 2008 is a Monday whereas it's actually a Tuesday- utterly bizarre!

I can get a fix simply by modifying the =DAY() formula by adding 1 to my formula [ie =DAY(A1)+1] but am wondering is this a "so called known issue" with Excel or has anyone else come across it? I have never previously come across this and consider myself to be an above average competency level user of the application.

View 6 Replies View Related

Counting Amount Incorrect Within A Date Range

Jun 8, 2009

I would like some help from someone on this subject if possible. I am running a stats report in a workbook where each member of staff have their own running totals of how much work they do and how much of that work is incorrect (per week). I have worked out that if I use the following:-

=SUMPRODUCT(('Barry Brooks'!$A$1:$A$2000>=$B$95)*('Barry Brooks'!$A$1:$A$2000<=$C$95))

I get the total amount of work. To explain the above, it returns the amount of times a date appears between two dates i.e B95 is week commencing date and C95 is weekending; therefore giving the total amount of work in that period.

What I am struggling with is that I need a formula to look at the above date range and then look at another column that has either a Y or N (for yes or no) and for the formula to count how many Ns there are (thus how many in that given range there were)

It appears to me quite a small ask for Excel but I cant seem to get it to work.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved