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