References Goes Wrong After Sorting

Feb 18, 2010

I have a problem with my references when sorting in excel. I have been searching for the problem, but did not found a useful solution.
I'm using excel 2003.

Let me try to explain my problem with a simple exampel.

I have a column with numbers and one with formulas pointing at these.
A B
2 =A1
4 =A2
3 =A3

Now I want to sort column A, and the column B should keep pointing at the original number. So I want this:
A B
2 =A1
3 =A3
4 =A2

But I get this:
A B
2 =A1
3 =A2
4 =A3

This output is what I would expect if I used absolut reference($).

Using offset(B1;0;-1) will work, but I cannot use this, since I will add/delete rows and the reference is not neccessarily next to it. Plus it can be a rather large sheet.

I cannot use dynamically name definition either, because it is intended for other users, and this will be too much work for them. What I basically need is a simple formula they can enter.

My sorting is done via a VBA macro, so if there is anything I can do through macro/VBA that will be okay as well, since I can do this without the user seeing it.

View 14 Replies


ADVERTISEMENT

Sorting Of List Appears In Wrong Oder

Apr 9, 2008

I've tried to sort a list using the sort 1st by and then by options, but it will only sort by one of the options. I've tried with a header row, without a header row and every way I can think of and still can not get it to sort by more than one column on this particular list.

View 6 Replies View Related

Sorting Worksheets With References To Second Worksheet

Apr 21, 2009

I have a workbook with two worksheets.

Worksheet one is "Company ID" info, in it are the columns:"Company Name", "street add", "city", "state".

Worksheet two is "Company Contact Person" in it are the columns: a reference to worksheet one column one "Company Name", and column two is 'Contact Person name'.

I want to be able to sort worksheet one at will, By city, or state, or any of the various columns. When sorting worksheet one I want the relationships in worksheet to to remain intact.

"Company Name" - "Company Contact Person".

View 6 Replies View Related

Sorting Sheet That References Data From Other Sheets (Google Sheets)

Jan 25, 2014

[URL] ....

I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.

View 1 Replies View Related

Changing Hard Coded Column References To Variable References

Oct 22, 2009

I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.

I have been working on this for a couple of days and even tried EE, but to no success.

I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")

View 9 Replies View Related

Converting Absolute References To Relative References In Formulas?

Feb 10, 2014

I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.

All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.

=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")

View 1 Replies View Related

Structured References And With Cell References I Get A Column Of Zeros

Nov 25, 2007

It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.

I have tried to do it with structured references and with cell references I get a column of zeros!

View 9 Replies View Related

Change Static References With Lookup References

Mar 2, 2009

I am using the dsum formula to sum some values...the formula in B2 is:

=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))

The Planes Entidades sheet the data is layed out like this: ....

View 9 Replies View Related

VBA - What Is Wrong With My For Each Next

Feb 17, 2009

I am using a For Each Next to cycle through a list of names, check certain conditions and then will be adding a further lookup (instead of Result = Result+1), but all I get is #Value!

Function maxbarometer(Name As String, Round As Integer, NameList As Variant, RoundRange As Range, RoundRangeTwo As Range)
Dim Roundname As String
Dim Result As Double
Dim NameColRound As Integer
Dim ListObject As Variant

Roundname = "Round " & Round
NameColRound = Application.WorksheetFunction.Match(Name, RoundRange, 0)
For Each ListObject In NameList
If ListObject.Value = Name Then
Result = Result
ElseIf Application.WorksheetFunction.VLookup(ListObject.Value, RoundRange2, NameColRound, False) = Roundname Then
Result = Result + 1
End If
Next
maxbarometer = Result
End Function

View 9 Replies View Related

Getting Wrong FinalRow

Mar 19, 2007

when i try to get the final row witht the following:

KROGrow = Cells(65536, 1).End(xlUp).Row

i am getting a row that is consistently 27 rows off of when i manually do control up? i am pasting data from and existing workbook into a newly created workbook in the sub. i would assume that since the destination is new that they would never be any data in this?

View 9 Replies View Related

VLOOKUP Returning Wrong Value?

Jul 3, 2013

I am trying to use the Vlookup function to return State name based on the corresponding state code.

I have the list of State along with the Codes in adjacent sheet, arranged in ascending order. I am using the Vlookup function to list the State name based on the State code.

Vlookup function: Vlookup(A4,Sheet2.A2:B51,1)

This function returns me a wrong value. It gives "Arkansas" for the state code "AZ", when it should be Arkansas.

Also, if I add "FALSE" as a 4th argument, it returns "#N/A".

View 9 Replies View Related

IF Formula Evaluates To Wrong Value

Jan 29, 2014

I have the following formula: IF(SELL_INVESTMENT = "YES" , 1,2) It returns not 1 or 2 but 0. The worksheet contains over 1000 cells. Trace Precedents verifies that the formula evaluates the correct cell and that it contains "yes". I created a very simple worksheet to test the formula and here it correctly returns 1.

View 2 Replies View Related

Wrong Dates In Database

Apr 18, 2014

When copying data from a website to excel, excel recognizes the dates wrongly.

About half of the dates he sees as: DD/MM/YYYY, while it should be: nothing/MM/DD.

For example: The cell contains the date: 01/04/2029 (The first of April, 2029)

While it should actually be: 04/29 (The 29th of April, no year!)

For all dates it starts with the 1, followed by a month, followed by a year (which consists of the year 2000 + the actual day), which is totally wrong.

About the other half of the cells, it just contains text with the month and the day (which are correct), but excel doesn't recognize this as a day.

For example: The cell contains the text: June 12

When I set the cell format to date (or any other format for that matter), nothing happens, so excel does not see this as a date.

View 1 Replies View Related

IRR Function Is Returning The Wrong Value

Nov 7, 2008

I'm trying to figure out an Internal Rate of Return for a spreadsheet. The answer is supposed to be: 29.42% however I'm getting 25.94%. Does anyone know what I'm doing wrong in my IRR function on the Profitability worksheet? I'm currently using Excel 2007 by the way.

View 4 Replies View Related

Copying To Wrong Column?

Oct 22, 2013

I have modified this micro I found in a forum. All I changed was the Range on Sheet 1.

[Code].....

It works fine except when it cuts to sheet 2 it starts at on column B I need it to start with Column C. What do I need to add ?

Attached File : Survey.xlsm‎

View 7 Replies View Related

Saving In Wrong Location

Sep 25, 2006

I have the following code which saves my worksheet with a name extracted from particular cells:

Public Sub SaveAsMaximoWO()
ThisFile = Range("AC5").Value
ThisFile2 = Range("E3").Value
saveName = ThisFile & " - " & ThisFile2
ActiveWorkbook.SaveAs Filename:=saveName

End Sub

This (for reasons I don't understand) saves the file to My Documents by default.

What I would like it to do is save to another folder within my documents as default.

View 14 Replies View Related

Decimal In Wrong Place

Mar 13, 2009

how to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.

View 2 Replies View Related

Click On Wrong Button

Apr 18, 2009

I have this workbook with 10 sheets. Each sheet has macros that are called from a floating toolbar. What I am trying to do:

I want to create a msgbox that warns me when I click in a toolbar's button to call a macro that not runs in the activesheet. Something like: "This code (or macro) doesn't match (or run) with the active sheet.

View 3 Replies View Related

Wrong Week Number

May 4, 2006

as such Excel (2003) is putting in the wrong weeknumbers in my spreadsheet, for example 02/01/2006 is listed as week 0 when in fact it is week 1, how can I get it too correct this problem?

I am using the following formula, could someone explain this formula in
english?

=CONCATENATE(YEAR(BM2),"_","w",IF((WEEKNUM(BM2)-1)<10,CONCATENATE("0",(WEEKNUM(BM2)-1)),(WEEKNUM(BM2)-1)))

View 14 Replies View Related

Time Replying Wrong

Sep 16, 2007

Thers no easy way to explain this other than looking at the sheet (which is attached), but basically i have a load time start time and end time.
In the example in the sheet the load time is 01:00
The start time is 23:00
The end time is 23:35

Th eload took 35 mins and was not late as it is going 01:00 next morning, yet the answer it replies is 'N' which makes it show as late.
Does anyone have a formula to slove this issue

View 10 Replies View Related

VBA Adding Wrong Dates

Dec 22, 2011

The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine

Code:
Sub reviews()
Dim cell As Range

For Each cell In Range("L1:L200")
If IsDate(cell) Then
cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value)
cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value)
cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value)
cell.Resize(, 10).AutoFill cell.Resize(, 11)
End If
Next cell
End Sub

View 3 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

LOOKUP Returns Wrong Or Odd Value

Mar 27, 2007

I have a very basic spreadsheet to calculate golfer handicaps based upon a course index. For some reason, my "LOOKUP" formula is not retrieving the data from the cell (column) next to the value I am searching...?

View 9 Replies View Related

Date Is The Wrong Century

Jul 29, 2009

I'm trying to parse a text file into Excel and the date is coming out as "1940" instead of "2040". In the text file the date reads "5/14/40". I've already changed my regional setting in Excel so that if I type in "5/14/40" it is understood to be "5/14/2040". For some reason this doesn't work when I parse the data though.

View 9 Replies View Related

Formula Returns Wrong Value

Feb 27, 2010

The formula below was written a couple of years ago and I just discovered an error. It returns an "S" for the value 0789 when it should be a "C", (Consecutive).

IF(OR(AND(ISNUMBER(MATCH({0,1,2,9},$O215:$R215,0))),AND(ISNUMBER(MATCH(MIN($O215:$R215)+{0,1,2,3},$O215:$R215,0)))),"C",IF(SUM(IF(FREQUENCY($O215:$R215,$O215:$R215)=3,2,1))=2,"DD",INDEX({"S","D","T","Q"},MAX(FREQUENCY($O215

******** ******************** ************************************************************************>Microsoft Excel - FL MID PLAY 4 CONSECUTIVES.xlsm___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutT215=OPQRST215078907893SSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Data Is Going Into The Wrong Cells

Jun 30, 2006

The macro was orininally set up for the 7 days of the week. Now I want to set it up for the 31 days of a month. It's set to pull information from a register sales journal. Here is the button macro-


Sub Button2_Click()
Dim sDay As String
Dim sShift As String

sDay = InputBox("Which Day? (1 For MONDAY, 2 For TUESDAY, ... 7 For SUNDAY)")
Select Case sDay
Case "1"
sDay = "1"
Case "2"
sDay = "2"
Case "3"
sDay = "3"
Case "4" ........................

View 10 Replies View Related

VLOOKUP Returning Wrong Value

Dec 14, 2006

I have this formula that returns a grade mark for a specific score in a test:

=VLOOKUP(D4,'Grade scale'!$A$34:$B$42,2)

using these values in the lookup table:

Low scoreGrade
0.0% U
25.0% G
35.0% F
45.3% E
55.3% D
65.5% C
72.0% B
78.0% A
85.0% A*

The problem comes where the Grade is A or A* as the Vlookup table can't differentiate the two. Anyone know how to modify the formula so that A and A* are seen as distinct values?

View 9 Replies View Related

Autofilter Wrong Date Format

Mar 14, 2014

I have a sheet called "interface" which people can input the date in cell A2.

The autofilter in sheet"originaldata" will filter the data according to the date in cell A2 in sheet "interface"

However, when i input the date 01 Aug 2009, the filter will give me 08 Jan 2009

VB:
Sub Date_filter()
Dim Inputdate As Date
Sheets("interface").Activate
Inputdate = Format(Sheets("interface").Range("A2").Value, "dd mmm yyyy")

[Code] ....

View 1 Replies View Related

IF Formula Returning Wrong Answer

May 1, 2014

I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.

View 10 Replies View Related

Vlookup Returning The Wrong Data?

Jul 10, 2014

I am working on a form to pull employee identifiers such as employee ID, store number as well as sales performance. I am using the below formula but the data that is being returned is not the correct data for the specific employee. C5 is the employee name (last name, first name) in a single cell. A sample of the spreadsheet I am trying to pull data from is attached. Some employees the formula pulls the right data and some it does not...

=IF(C5="","",VLOOKUP(C5,'Raw Data'!A3:N15,2))

View 3 Replies View Related







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