How To Sort Data In Excel If It Has Cell References In It

Jun 11, 2013

I created an excel workbook with multiple cell reference throughout the various worksheets. I did this because I was creating a dependent drop down list. When I try to sort my data alphabetically, all my cell name references get changed. How I can sort alphabetically without losing my cell name references?

View 7 Replies


ADVERTISEMENT

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

Apr 16, 2013

I am using Excel 2010. I am a novice user.

I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.

pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d

[Code] .........

Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.

pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d

[Code] ......

So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.

reg_[0-9]+_+[0-9]+/d

The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".

Similarly folder paths names can contain "_" so can't split string on this either.

As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.

I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:

Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function

If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?

Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g

26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d

So my table would show the name "data_out_reg" and the range of values 8-32

View 1 Replies View Related

Excel 2007 :: How To Copy A Cell Without Auto-adjustment Of Fix Cell References

Jun 15, 2014

Assume I have a cell M24 with a formula like

=M10 + $H24 - $I24*0.35

As you can see B10 is a fix reference (due to omitted $) which should NOT be auto-adjusted but be kept.

Now I want to copy the formular to lots of cells below cell M24. therefore I mark cell M24 and click copy in context menu.

Then I drag/expand the blinking cell border to lets say the 20 cells below. As I result I expect e.g. in cell M25 a formula like

=M10 + $H25 - $I25*0.35

Unfortunately I got

=M11 + $H25 - $I25*0.35

So the fix reference is adjusted as well.

How can I tell Excel 2007 to NOT auto-adjust fix references in formulas?

View 2 Replies View Related

Preserve Formula References After Sort

Oct 23, 2006

I have noticed that if I create a row and put a reference formula in one of the cells (i.e. in Cell B3 I have "=B4", to show contents of B4), then after I do Sort and Row 3 moves else where, formula in what used to be B3 no longer points to B4, but rather to B<new row + 1>.

Absolute reference doesn't seem to be an option (i.e. "=$B$4") since after sorting rows in question the cell references in my B cells do get screwed up. I guess I want to bind a certain cell to a certain other cell, and keep that bind no matter where I move the row.

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

Excel 2007 :: Auto-Update Macro Cell References

Jan 19, 2013

In Excel 2007.

I have a macro that is set to clear a range of cells. If I make a change to the worksheet (such as adding a column) that moves those cells in the worksheet, the worksheet formulas update, but the macro cell references do not. How can I make my macro update in accordance with changes in the worksheet?

View 3 Replies View Related

Excel 2007 :: Crashes When Cell References Using SUMIF To Another Workfile Are Above Row 65k

Nov 28, 2013

I have 2 excel files (both .xlsx) and in the first file i am creating a reference to cells in the 2nd file using SUMIFS function. The 2nd file has 275,000 rows of data. I can create the formula correctly, however when i save the file, it just crashes every time (says not responding). If i create simple reference (=A275000) then its fine so im not sure what the issue is.

View 1 Replies View Related

Excel 2007 :: Why Does Using Cell References In Formula Give Incorrect Value

Sep 30, 2011

Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:

Cell A1: 0.003529
Cell B1: 0.48

If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.

Why does using cell references in my formula give me the incorrect value?

View 2 Replies View Related

Excel 2010 :: Dynamic Range - Cell References Changing In OFFSET Function

Apr 12, 2012

I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).

P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.

Here is my broken function:

=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))

View 3 Replies View Related

Cell References Data That Doesn't Exist

Mar 31, 2008

When I use the formula:

View 12 Replies View Related

Pasting Data From One Sheet To Another With Formatting And Cell References

Feb 11, 2010

I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.

One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.

I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.

Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.

A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.

By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.

View 5 Replies View Related

Sort Survey Data In Excel

Apr 4, 2013

I want to analyze a survey which I made. Below you see an example of how I structured the answers.

Now I would like to rank the answes (rows 2-6) in a list according to often each item was mentioned.

I could do transpose the data manually and delete items which were mentioned various times. However, since it was a pretty big survey, it would take to long. Is there a formula to do it?

Original:

Person A
Person B
Person C

Water
Water
Sugar

Washing
Washing
Pasta

Boxes
Milk
Water

Frozen
Vegs
Fish

Cleaning
Cheese
Water

Example of how it should look like:

Water
3 (times mentioned)
100% (because everone mentioned it)

View 3 Replies View Related

Excel 2013 :: Sort Data Not Working?

Aug 14, 2013

Ok just started using Excel 2013.

Trying to sort 3 columns:

Column 1:
A1/B1
A2/B2
A3/B3

Column 2:
Team 1
Team 2
Team 3

Column 3:
1
2
3

Column 3 is just a ranking from 1-30. So I select all the data in columns 1 and 2 and hit sort from largest to smallest and it looks like excel computes, but nothing changes and it doesn't sort from largest to smallest? A

View 6 Replies View Related

Excel 2007 :: Data Sort In Ascending Order

May 23, 2012

Excel 2007.

The first part of this code is fine and completes all the borders.
The second part is to the all the workbook by Data Sort in ascending order starting with

Column C
Then Q
Then column V

But also knowing to search to the last line. I think i might be close but not close enough for this to work.

Sub Macro2()
Dim LR As Long, i As Integer
Dim mysheet As Worksheet

Sheets("Test").Select
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A9:AD" & LR)

[Code] .......

View 2 Replies View Related

Excel 2007 :: Cell Text Sort

Nov 17, 2011

I am struggling with doing a sort, of a group of alpha characters within a cell (Excel 2007). My data was exported from Oracle 8 as a varchar2 and consists of letters.

Example of what I have
BAC
GHL
DFE
MEG

Example of what I want
ABC
GHL
DEF
EGM

All I could find were these complex looking solutions involving cursors, plsql, etc. I'm hoping there's a simple solution, maybe within Excel. I've tried different formatting, made sure no other characters were sharing the cells, and tried typing fresh data into a cell (instead of the exported data).

View 8 Replies View Related

Excel 2010 :: Sort Growing Column Starting Under Header Row At Cell C5

May 7, 2014

find macros to sort fixed ranges but not an entire column with existing header rows from 1 to 4.

Added to that is the need to keep the code compatible with Excel 2003 even though I have Excel 2010 at office (it's a file that needs to be "openable" in both versions so the vb code needs to be in XL 03.

Column currently starts at C5 but goes down to C47, at this point. But it's a growing list. Some of the rows are blank at the bottom, too.

View 2 Replies View Related

VBA Code To Sort Data - Run-time Error When No Data To Sort

Mar 4, 2010

I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.

Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer

zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

View 9 Replies View Related

Sort Data Alphabetically In One Particular Cell Without Retyping Data?

Jun 6, 2014

I would like to be able to sort the data in this one cell alphabetically without retyping the data .ie use a sort function but only for a cell.

For Example cell A1 contains Chris Brown Andy

No commas there. Names are seperated only by spaces.

What function should I use in order to get Andy Brown Chris ?

View 5 Replies View Related

Sort Data Alphabetically In Particular Cell?

Jan 30, 2014

I have a long data in a single column with mixed letters. and column A (or H, K etc.) like this:

e1.JPG

How can i sort that cells to alphabetically like this?

e2.JPG

I already tried a function discussed this topic, but it's not working: [URL] .....

View 6 Replies View Related

Click On Cell To Sort Data

May 24, 2007

I have a softball spreadsheet with columns of ab, r, h, 2b, 3b, hr, bb, so, Avg. rows of all the players on the team. I'd like to be able to click on the cell containing any cloumn heading and have the data sort out from best ot worst of that catagory. Is it possible to re-sort data simply by clicking on a certain cell, and if so can it be done for 8 different cells?

View 9 Replies View Related

Sort Data Without It Moving Cell Colors

Apr 30, 2009

I have a sheet with alternating colors (gray,white) for the rows. This makes it easier to read each individual rows data, just like some printer papers. The problem i have is when i sort the data it takes the background color with it and i end up with a mess. does anyone know how to get around this.

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

Excel 2003 :: VBA To Bind References?

Sep 20, 2012

Is there a way to attach the VBA references on opening the workbook. The users of my workbook can be both excel 2003 users and excel 2010 users.

I want my macro, when you open this specific workbook. Check the Application version and if the version is 2003, than attch Microsoft ActiveX Data Objects xx Library and Microsoft ActiveX Data Objects (Multi-dimensional) xx Library and if 2010 than Microsoft ActiveX Data Objects xx Library and Microsoft ActiveX Data Objects (Multi-dimensional) xx Library.Where xx is relevant version no. for each 2003 and 2006.

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

Excel 2010 :: Sort Worksheets Alphabetically And Keep The Data In Worksheets

May 15, 2013

I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.

View 2 Replies View Related

Change From Absolute To Relative References With Excel Macro.

Sep 6, 2009

How to change from absolute references to relative references.

Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365

View 12 Replies View Related

Excel 2007 :: Variable References To Multiple Workbooks?

Jan 10, 2012

i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.

these workbooks all have the same name with the date at the end

example:
workbook 06-01-2011
workbook 06-02-2011
all the way to
workbook 12-31-2011

usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant

i have seein INDIRECT of the cell changes but nothing for the workbook name chaning

here is what i am trying to do:
='C:File Path[WorkbookX.xls]Sheet1'!A1
where the X WorkbookX is variable (the date at end of file name)

i have a list of all workbook names or just the dates and would like to do something like:

=D1&$A$1
where D1 is the full path name
or
='C:File Path[Workbook"D1".xls]Sheet1'!A1
where D1 is the date name

using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.

my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks

View 1 Replies View Related

Excel 2007 :: Formula In Worksheet Contains One Or More Invalid References

Feb 7, 2012

Everytime I save my workbook, I get a dialog box that says:

"A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a vlid path, workbook, range name, and cell reference."

First off, it say's "this" worksheet no matter what worksheet is active.

Usually the culprit is a screwed up name that has a #REF in it somewhere. Not this time. All of the formulas appear to be working fine.

Is there an audit feature that will find the errors for me? (Excel 2007)

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

Excel 2013 :: Calculating Value Based On Multiple References And Dropdowns

Jul 19, 2014

Using Excel 2013.

I have two sheets in a workbook.

Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result

Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:

Mod 1
Mod 2
Mod 3
Mod 4

Text 1
0
0
0
0

Text 2
2
0.5
0.25
0.25

Text 3
7
1.75
0.875
0.875

What I want to happen is that the Result field value on table one is the result of:

Distance*(Modifier 1 + Modifier 2 + Modifier 3 + Modifier 4)

For example:

On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:

6*(7 + 0 + 0 + 0)

This might be exceedingly simple, but I just cannot wrap my head around how to do it.

View 1 Replies View Related







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