Formula Works On One Tab But Not In Another

Oct 14, 2008

I want to do a vlookup between two tabs on a date in cell A1 eg 01-Apr-08
The lookup needs to be on month and year so I'm using

=Month(A1) which gives an answer 4 in cell A2
=Year(A1) which gives an answer 2008 in cell B2
I then oncatenate the results (=A2&B2) to get a unique reference 12008 - works ok

On the second tab, I've repeated the formula, I get an answer of 1 and 2008 but when I concatenate, I get a #VALUE!

I've tried
"text to columns"
unchecked Protect on the cells
worksheet is not protected

on the 1 and 2008 and concatenated result

View 9 Replies


ADVERTISEMENT

Formula Works On One Computer But Not Another

Nov 12, 2009

I have a odd problem. I have workbook that I made I placed this formula in cell A1

View 3 Replies View Related

Formula Works But Not For Formatting

Jan 23, 2012

I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen.

The formula:

=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)"",INDIRECT("a"&ROW())="Item1")

View 5 Replies View Related

Formula Works On One Worksheet But Not The Other?

Jan 25, 2014

The formula I'm using is

Code:
=SUBSTITUTE(OFFSET(A10,-2,0),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,5),VLOOKUP(OFFSET(A10,-1,0),prim!$D$1:$I$263,6))

But it doesn't work at all on one worksheet, half works on another, and works sometimes on another.

In the worksheet attached called workbook 4, it works a15 sheet 2, but not a10 sheet 2. But that was not always the case. In the worksheet attached justification copy, it works in a14 sheet 4 but not in a10 but for a good while it didn't work in both. In my own private doc it doesn't work in both cases.

[URL]

View 6 Replies View Related

Sum Formula That Works In 2003 But Not 2007

Jun 9, 2009

I have this formula :

=SUM(F25:M25*$F$6:$M$6)

It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.

How can I re-work this for xl2007?

View 3 Replies View Related

Formula Works In VBA Screen, But Not On Worksheet

Dec 2, 2007

Private Sub Zero()
If Range("G8").Value = "" Then
Range("G8").Value = "0"
End If
End Sub

I have this listed in as code for VBA. If I hit the "Run Sub/User Form" button on that screen, with the field G8 having nothing in it (having hit "Delete" just prior), it resets the field to "0".

But when I hit delete on the worksheet, the field just sits there......no zero, no nothing.

View 9 Replies View Related

Formula Rectified And I Find Myself Not Knowing How It Works

Mar 20, 2007

I have a nifty formula brought to me by one of the excellent members on this board. It works perfectly for what I want, but I must admit I cheated, I have a problem that was similar to the problem this formula rectified and I find myself not knowing how it works.

=IF('IIC Transfer Status'!O139="x",MIN('IIC Transfer Status'!K139+10-WEEKDAY('IIC Transfer Status'!K139-{1,3})), "" )

What Im wondering is if someone could enlighten me as to what is happening in this function, so Im not as blind to assume it is magic.

Eventually I need it to be customed tailored to work in this way: I have a column of dates, I want it to look at column A and if there is a date in it ADD 4 WEEKDAYS (business days) and return the resulting date.

i.e date in column a is march 19 plus 4 business days. Result in column B march 23.

If someone can explain how this string works that would be great, or if someone could just post a suitable function, that would work for now.

View 9 Replies View Related

Formula Works A Treat Except When Text Is Entered

Nov 10, 2008

The problem i have is with the sum offset function. The formula i use is to sum up the totals for each page of an accounts document. At the top of each page there is a column with " £ p " in it and i use that as the basis of the formula. This formula is entered in the bottom of every page to calculate the total for each page.

=SUM(OFFSET(F211,-(ROW(F211)-MATCH("£*",$F$1:F210,1)-1),0,ROW(F211)-MATCH("£*",$F$1:F210,1)-1,1))

This works all the time but when text is entered into column F (the column i'm adding up) it messes up with the formula. the formula will instead find the £ p but for a page 2/3 pages before the one i'm calculating on. This formula only faults when text is entered. Another thing; this excel sheet i'm working on is an output from a different computer program. it is outputted as csv file i think.

Like i said the formula works a treat except when text is entered. The obvious way around this not to enter text but that is not an ideal option. I cannot think of another way of calculating the total for each page although i'm sure another exists.

View 9 Replies View Related

Formula Works On Numbers But Not On Time With Format Cell?

Jul 27, 2014

The formula works on numbers but not on time with format cell : [u]:mm:ss

HTML Code: 
8
7
6
11
12

HTML Code: 
8:00:00
8:00:00
6:00:00
3:00:00

[Code]....

View 1 Replies View Related

Array Formula Works In One Portion But Not After Being Altered And Moved

Dec 18, 2013

I figured out exactly what I wanted to do and got it to work in a test excel sheet. However, when integrating it into the actual workbook I wanted, I was unable to get it to work. So, I used the same cell references I need to work in my actual workbook and pasted it all back to the test excel sheet.

So, the test excel sheet has the working formulas up at the top, and a duplicate of what I need to work in the actual cells I need them to work in. Changing the shift start time should group any persons with the same shift togethor. I'm still fairly new, but I think the only portion that could have been changed is the portion that says 1:1.. I figure that is relative to the array so it shouldn't be changed, but not sure what else to do.

Here's an array formula that works:

[Code]....

Here's the array formula to be in the correct cells that doesn't work:

[Code] ....

How to to get it to work in the different cell area.

Attached File : Shift.xlsx‎

View 3 Replies View Related

VLOOKUP Formula Works But Not If Type In New Data In Cell?

Aug 23, 2013

I have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)

When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".

If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.

View 1 Replies View Related

TWo Way Cross Reference Formula Works But Not Across Entire Data Range

May 21, 2009

I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.

View 2 Replies View Related

IF Blank Or If Not Blank Function That Works With Adjacent Cell With Formula In It?

Aug 12, 2014

I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).

What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)

View 5 Replies View Related

Array Match Formula Fails But Simple Match Works?

Feb 5, 2014

In sheet 1, I have

Col A Col B
John Sedgwy - R

In sheet 2, I have the following names

Col A Col B
Peter Walker
John Sedgwicky

When I did an array match -> ={MATCH(1,(TRIM($a2)=Sheet2!$A$1:$A$2)*(left(b2,5)&"*"=Sheet2!$b$1:$b$2),0)}

gave me an error but when I did an individual match to both John and Sedgw, it works.

View 4 Replies View Related

VLOOKUP Only Works On One Value?

Mar 25, 2014

some call databases from my internal telemarketing team, which are all on Excel databases. These contain multiple contacts within the same organisation, with no "unique identifier", i.e. there is no information specific to individual records EXCEPT their email address - and unfortunately, not every contact has an email address, which would prevent using VLOOKUP, which is the only function I can use to perform this type of lookup.

I have five separate spreadsheets, plus one master database spreadsheet. I have added five columns to the end of my master database, and I would like to use each column to identify which spreadsheet(s) the individual records appear in - normally this would be possible by using a VLOOKUP in each of the five new columns, selecting a unique identifier, and using the VLOOKUP function for each separate spreasheet. However, without a consistent unique identifier, I do not know another function which would allow me to use multiple identifying info (e.g. "FirstName" + "Surname" + "Company") to perform this task.

View 1 Replies View Related

Add New Sheet Only Works Once ()

Nov 30, 2006

If SheetExists(Sheets("data").Range("V" & x).Value) Then
'do Nothing
Else
Sheets.Add
ActiveSheet.Name = Sheets("Data").Range("V" & x).Value
End If
ProjectReview.Show

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

I'm trying to add a new sheet, if a sheet with that name doesn't exist already.
Which I have a feeling is pretty simple!. My Error is occurring at the * in the code. Basically I am adding a sheet per record. I'm using a unique identifier for each tab. The first run through, it creates a new sheet and opens a user form. When I close out that form, it returns to here and fails to work the second time through. I'm fairly confident it's because I'm using a select statement, I've also tried using it this way, but the set statement is invalid because it's not refrencing an object....

View 2 Replies View Related

How AutoFilter Works

Jul 6, 2009

I need some information about How the auto filter in excel works?

Excel shows a drop box with list of unique values in that column, if i select one value only rows having that value are displayed.

Are other rows "hidden". I tried to unhide these rows but those are NOT hidden. What actually happens to rows that don't satisfy the filter criteria?

View 9 Replies View Related

Add-in UDF Works In New Workbooks But Not In Old Ones

Nov 24, 2009

I wrote a UDF that does some advanced lookup trick in a particular workbook. That workbook has to be open during this process of course. I used to have a copy of this UDF in several workbooks where I need to use it. This turns out to be bothersome since I needed to update the UDF frequently. So I decided to make an add-in.

I then copied the UDF to a new workbook, save it as an add-in (XLA), then go into Excel Options - Add-in (I use Excel 2007) and enable the add-in there. I then remove the UDF from each and every workbook that used to have it.

Now I close everything.

I then open the lookup workbook that needs to be open if I were to use the UDF. Here is where I don't understand. If I open a new workbook and use the UDF, it works fine. If I save the new workbooks then reopen them, the function still works. However if I open some old workbook where the UDF used to be and try to use the UDF, Excel gives me #Name? errors.

View 9 Replies View Related

Vba Works In Windows But Not In Os X

Jan 16, 2007

I have put together the following code and it works fine on my PC. However, we are a mainly Mac based company and I nead it to work with a Mac. I am using the Actual ODBC Driver for Mac to give me a ODBC connection. I can do the query in excel but setting it up as a macro just will not work.

Sub Sales_Query()
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Range("B2").Select
Dim area As Variant
area = Range("B2")
With ActiveSheet.QueryTables.Add(Connection:= Array("ODBC;DSN=my_database;Description=My................................

View 2 Replies View Related

Macro Works Stepping Through But Not When Run On Its Own?

Jul 21, 2014

I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.

Here is the code:

VB:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy

[Code]....

I have two questions:
1. Why did this work in 2007 and not in 2013?

2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?

View 3 Replies View Related

PDF Macro Works With Windows But Not On Mac

May 18, 2014

I've sent the file to 2 different windows computers and it works fine, but doesn't work with the two macs that I've sent it to.

This is the code:

[Code] .....

View 2 Replies View Related

Understanding How FindFirst Works

Mar 5, 2008

How do I use Find to locate the first occurence of a particular value in a cell, and return the row that the cell is in?

View 14 Replies View Related

Code Works In One Workbook But Not Another

Feb 19, 2009

This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?

Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub

View 2 Replies View Related

VB Works On One PC, Not On Another (Run-time Error 9 )

Apr 15, 2009

I run PCs at work and at home. Both machines run Windows XP and Office 2007

Home PC: Windows XP Home edition Version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO

Work PC:Windows XP Professional version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO

The VB was created on the home PC. It works. On the Work PC it crashes on the line with bold. Run-time error 9 - Subscript out of range. This have never happened before (or with any other VB routine). I also changed fileNew to include the full path, but same error. What has caused this? I suspect it’s the 'fileNew'.

View 4 Replies View Related

VLOOKUP Works In Some Cells But Not Others

Apr 13, 2006

Excel 2000

Really simple data sheet: list of names with homerooms
Lookup table: list of homerooms with teachers. (named "Range")

=VLOOKUP(E2,Range,2)

Some of the cells return the correct name, others #N/A.

As far as I can tell they are all formatted exactly the same; I've tried
several different formatting options. The correct ones stay the same; the no
data ones do not change either.

View 9 Replies View Related

Rounding Function Works On 1 PC But Not Another

Dec 1, 2008

I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).

I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".

I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.

I have attached a sample of the offending spreadsheet. The rounding function is in column "B"

View 10 Replies View Related

Forcing Fullscreen Works Sometimes?

Dec 6, 2013

I want to have a fullscreen application all the time, so for this I've added to things in my code:

1) Application.DisplayFullScreen = True in Workbook_Open

2) Same in WorkBook_WindowsResize

I open my workbook, it goes fullscreen. Until now all OK. However, if I press ESC or doubleclick on the topbar, it goes out of it.

Now the weird thing: if I click the "Restore window" button (which is right under the application maximize button that incidentally I have disabled) it snaps on fullscreen and THEN it keeps this state forever, even on ESC/doubleclick. Which is what I want.

So my question is: what does exiting from fullscreen and going back in change so that the workbook event starts triggering, and how I can replicate it programmatically? I've already tried WindowState = xlNormal followed by xlMaximize, it doesn't change anything.

View 3 Replies View Related

Macro Works On One Sheet But Not The Other

Mar 31, 2014

In the following user defined function

Code:
Function CA2GT(ByVal S As String) As String
Dim x As Long, CA As Long, Total As Long
CA = InStr(1, S, "CA", vbTextCompare)
For x = CA To Len(S)
If Mid(S, x, 1) = "(" Then

'replace arguments: oldtext, start at number, number of characters, new text

[Code] .......

It works on one workbook but not another workbook. Why?

View 5 Replies View Related

IF Function Only Works With 7 Different Selections

Aug 26, 2002

I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.

Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)

View 9 Replies View Related

Mod Function Works Wrong

Dec 28, 2006

I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.

And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.

So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1

But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6

View 9 Replies View Related







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