Numbering For Coordinates
how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).
The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculating Distance Between Coordinates
i use a Trimble Total Station Survey equipment, This is used for suveying and measuring distances between points on jobs. These points can be exported onto the pc as a text file and imported onto excel, the file has a northing and easting to locate the point these are comma separated. I would like to know if excell is capable of working out a distance betwen two of these such points by using the two pairs of northing and easting coordinates. The maximum distance between two points is no more than 50 meters. If for example when imported onto excel point 1's coordinates are locted in cells A1 and B1 and points 2's coordinates are locted in cells A2 and B2.
View Replies!
View Related
XYZ Coordinates Graph 3D
I have the following data: X Axis - Years (2004, 2005 ,...) Y Axis - Quantity (imported, exported, produaced) Z- Axiz - Name of the products I want to make a graph with teh X, y,z axis in excel. When i use the 3D graphs in excel, it is not taking the z vales connected to teh cell.
View Replies!
View Related
Calculate Angle From X, Y Coordinates
I have a userform, on it is a very large command button with a picture of a map loaded on the button. The button is 500 width(X) by 500 height (Y), so the center of the button would be at the location 250 (X) by 250 (Y). The button is coded for mousemove to get the X,Y cooridates and when you _Click the button a textbox gives the distance to the point(mousemove) from the center of the button. All that is good, but now I'd like to find the degree of the point referenced to a compass, ie, if the point is at 250 (X) and (Y) is less than 250 then the point would be due north at 0/360 degrees and if the point (Y) is greater than 250, it would be south of the center of the button. I hope this make sense. It's been a long time since trig. I've been searching but can't seem to find the correct formula to figure this out. Here's the other problem, I'm doing this in powerpoint so I'm not sure if cos or tan functions are loaded in this program. I am posting this in the xl forum because I figure that's where most of the people who could figure this out are. If this is in the incorrect forum I can try to move it,
View Replies!
View Related
Convert Lat/longs Coordinates
I'm trying to create a spreadsheet that will convert coordinates from DDMMSS.00 to DDMM.000. I found another thread that discusses this same issue, but I was unable to make it work for me. I'm attaching a sample spreadsheet that basically shows what I'm trying to do.
View Replies!
View Related
Calculate Shortest Distance From Coordinates
I want to use a User Defined name look up its corresponding X/Y Coordinates , reference a list organized by categories, then go through each name in the list finding each's X/Y Coord Calculate distance. SQRT((X-X)*(X-X)+(Y-Y)*(Y-Y)) where X and Y are Coordinates & Return the name of the smallest Value.
View Replies!
View Related
Finding The Hypotenuse Of From Two Pairs Of X,Y Coordinates
I have a map with several waypoints and would like to find the distance between any of these two waypoints. Sounds simple, but I have having an issue with the later parts of the excel file. Let me explain further. There are over 100 waypoints on this map we have. Each waypoint has an, seemingly arbitrary, X-coordinate and Y-Coordinate. However, if you divide the coordinates by [X], they turn into miles. It should go without saying that finding the distance between two waypoints is rather simple mathematically, but I having an issue figuring out how to pull two random waypoints and having the distance automatically calculated. So far, I just have column A with the waypoint name and with column B and C having the X and Y coordinates respectively. Is there anyway I can create some sort of call function that when two waypoint values are entered it would automatically calculate the distances?
View Replies!
View Related
Extract AutoCAD XYZ Coordinates
I am trying to extract (and separate into 3 columns) the numbers only, positive or negative portion of (XYZ) Coordinates copied from AutoCAD and pasted into column A X = 96179.9699 Y = 61973.9793 Z = 1368.0025 In column B,C and D, I have tried to use Find Mid and other means
View Replies!
View Related
Count Geographic Coordinates Within A Range
I have 2 columns of longitude and latitude coordinates. I want to count coordinates within certain ranges. A bit like using the Frequency function but for 2 sets of values and 2 bins arrays. This would mean I could create a table with latitute bins as the row headers and longitude bins as the column headings. Would anyone know how to do this?
View Replies!
View Related
Calculate Relative Vector Coordinates
I am running a fairly complex simulation at work and have quite a few different functions in it. For the simulation to work properly, i need to have the Solver add-in and the Analysis ToolPak installed. Everytime that i try and use the simulation on a different computer (or if anyone else tries to run it) the add-ins must be enabled again. Is there a way to set up a macro to enable them when either i open the simulation (first preference) or when a button is pressed (2nd preference).
View Replies!
View Related
Data Type Of Coordinates Of 2-dimensional Array
I have a big array "DataArray" and want to access it: For i = 1 To 4 variable = DataArray(SourceArray(i)) Next i "DataArray" has two dimensions, so SourceArray has to consist of data like this: SourceArray(1) = 1,2 sourceArray(2) = 2,4 etc What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.
View Replies!
View Related
Modify Free-form Shape Coordinates
Right now I'm modifying a figure based on size data in an excel table. I'm modifying the X/Y coordinates of the points of the freeform shape with VBA, however, I have to use absolute x,y coordinates. I'd like to be able to move the shapes throughout the sheet. With fixed coordinates, this makes it difficult. I'm hoping there's an easy way to it
View Replies!
View Related
ActiveCell.Address - Discarding Fixed Coordinates
I'm attempting to write a piece of code that builds a formula dependent on the location of the cell it is being created in. The relevant line of code currently reads as follows: ActiveCell.Offset(0, 5).Value = "=" & ActiveCell.Offset(0, -5).Address & " & " & ActiveCell.Offset(0, -6).Address Which when activated in cell A3, will enter the formula =$A$3 & $A$2 However, I would like to know if there is a way of automatically building a formula like this, only without the absolute references. Reason being, this formula is used in a large table, which is sorted in date order automatically when the sheet is activated. Since the code generated will point at the row it was generated on, when the table is sorted, this formula will calculate the incorrect result.
View Replies!
View Related
Minimum Distance Calculation Of Geographic Coordinates Using Array
I am working on two spreadsheets, one we shall call Target Sheet and the other Reference Sheet. On the target sheet, I have a set of 200 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site. On the reference sheet, I have a set of 900 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site. Note that of the 900, 200 are the same from target sheet, other 700 are different sites. Using the spherical law of cosines, I can calculate the distance between a set of coordinates on target sheet agianst a set of coordinates on reference sheet. This allows me to see the distance from one geographic site, to the other. This was easy. The formula I use is as follows: ....
View Replies!
View Related
Getting Max Value Of A Point Inside A Square Defined With X,y Coordinates
I have a square which is defined with x,y coordinates (roughly 2000 points), and for each point I have a value. And I have to divide this square in to 9 equal sub grids, and find the max value in each sub grid. I have all the coordinates for the sub grids. But I can’t get the max value of the specified cells. I am thinking something like this: if 0<x>10 and 0<y<10 then it is sub grid 1 and I want to find the max value there and if 0<x>10 and 10<y<20 then it is sub grid 2 and so one…..
View Replies!
View Related
Button Position/Coordinates On Worksheet. Discrepancy Between Versions
Add Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left; Set sbut = Sheets("Listings").Shapes.AddFormControl(xlButtonControl, _ Range("J" & count).Left, Range("J" & count).Top, 50, 12) When I try to find the row of the clicked button using the following code; Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like; Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate count = ActiveCell.Row If Application.Version >= 12 Then count = count + 1 End If Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.
View Replies!
View Related
Random Numbering
I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once. I am putting the formula in column B.
View Replies!
View Related
Auto-Numbering
i have formulas in a range L5:L15 which sometimes return some value and sometimes zero. i want to give them auto numbers in column M in a way that it should only count the cell which has some value. suppose formula in L5 returns some value, L6 also then L7 & L8 have no value(but formula persists), cell L9, L10, L11 has values then L12 has no value L13, L14 has value and L15 has no value (but it has formula in it) values in these cells changes and some goes to zero and some return values. now i want to give them Auto Numbers in a way that cells with some value should only be considered.
View Replies!
View Related
Numbering In Forms
I have created a form to input parking ticket data to a spreadsheet, it all works exactly as i want it to, but i really need it to tell me the next available number or empty line, so i can use that for filing and audit purposes, ideally i would like it to do sequential numbering, but i've been looking for weeks and cant find a soloution, i have basic knowledge of VBA and i'm really struggling with this,
View Replies!
View Related
IF Statments And Numbering
Heres an example of what I'm trying to do, if I select form a list a certain name (i.e. "Plt") then I want it to populate a list of numbers (1-102) and the same with "SO" populating numbers 1-119. and here is what I have so far =IF(OR($F$1="Plt",$F$1="SO",$F$1="Plt LR",$F$1="SO LR"),"1.") Is there anyway of making excel do this?
View Replies!
View Related
Numbering System
Wondering if there is a formula for Excel that could replicate a numbering format like in Word? Example: A1.1. A1.1.1. A1.1.2. A1.2. A1.2.1. A1.2.2. A1.3. and so on... Idealy I would like to go farther than the 3rd level.
View Replies!
View Related
Numbering Macro
why the Macro below works fine when the spreadsheet is not filtered, but once you filter the spreadsheet it does not work. and if possible a solution. Sub Count() Dim MyInput As Integer MyInput = InputBox("Enter Start Number") MsgBox ("Start number is ") & MyInput mycount = Selection.Rows.Count MsgBox mycount ActiveCell.FormulaR1C1 = MyInput For Num = 1 To mycount ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = MyInput + 1 MyInput = MyInput + 1 Next Num End Sub
View Replies!
View Related
Sequential Numbering
I have a workbook with two worksheets. Worksheet #1 is a form that will be populated with data and saved as a new worksheet, then cleared and used repeatedly as a master form. Worksheet #2 is a log / register of the unique forms completed and saved from the master each time. I need to assign a unique sequential # to each form when it is saved and record this number in a column on Worksheet #2 (the Log). I am using some macros for the copy work but struggling with the auto-numbering of the forms when completed and saved.
View Replies!
View Related
Numbering By Group
i have items listed in groups and need to number them 1111 1111 1111 1222 1222 1222 1222 1444 1444 in the column beside this i need these items to be numbered 1 1111 2 1111 3 1111 1 1222 2 1222 3 1222 4 1222 1 1444 2 1444
View Replies!
View Related
Numbering A List
I'm trying to make a sequential resultlist starting with nr 1, 2, 3, etc under the column: Rank ? This should be part of a macro, so autofill is not an option... As you can see, the number of rows are different from each group, and starts with nr 1 for every group. (Some formatting became all wrong posting this.........
View Replies!
View Related
Sheet Numbering
I'm wondering if this is the way things work and there's nothing to be done about it (but I doubt that). I have a workbook that I load data into from a csv file. The csv file is "divided" into regions, and I want each region's group of data to be loaded into a separate sheet. To be on the safe side, I delete all the sheets before loading the data with the following code that I found in this forum Sub delete_all_sheets() Dim sh As Worksheet Application.DisplayAlerts = False For Each sh In ActiveWorkbook.Worksheets If sh. Name <> ActiveSheet.Name Then sh.Delete End If Next Application.DisplayAlerts = True End Sub Then, for each new region, I create a new sheet with the following code On Error Resume Next sheet_nr = sheet_nr + 1 Sheets(sheet_nr).Activate If Err.Number <> 0 Then ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) End If On Error Goto 0...............................
View Replies!
View Related
Restart Auto Numbering
I have just successfully added a code to Visual Basic in order for it to insert a sequential number automatically upon opening the worksheet. It works great, but how do I restart the numbering now that I know it works?
View Replies!
View Related
Auto Numbering A Tag Name
I would like to know if there is a way to Auto number a text. I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down. Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on
View Replies!
View Related
Insert Bullets And Numbering
Is it Possible to Insert Bullets and Numbering in Excel. Especially Bullets And what is the Easiest way to insert Bullets. Sheet2 ABC1ItemsWant this2BindersØ Binders3Pen SetsØ Pen Sets4PencilsØ Pencils5BindersØ Binders6BindersØ Binders7PenØ Pen8BindersØ Binders9BindersØ Binders10BindersØ Binders11PenØ Pen12PencilsØ Pencils13DeskØ Desk14PencilsØ Pencils15BindersØ Binders16Pen SetsØ Pen Sets17BindersØ Binders18BindersØ Binders19PenØ Pen20Pen SetsØ Pen Sets21PencilsØ Pencils22PencilsØ Pencils23BindersØ Binders24DeskØ Desk25PencilsØ Pencils26Pen SetsØ Pen Sets27BindersØ Binders28PenØ Pen29BindersØ Binders30PencilsØ Pencils31BindersØ Binders32PencilsØ Pencils33PencilsØ Pencils34PenØ Pen35Pen SetsØ Pen Sets36BindersØ Binders37Pen SetsØ Pen Sets38PencilsØ Pencils The "Ø " Indicate Bullets that means " Ø in Column C
View Replies!
View Related
Auto Consecutive Numbering
I have a form that I use often, but numbering is slow because I go in and number the form, print, go back and put in next number, print, etc. Is there a macro or formula that will automatically update the consecutive numbers when I enter or print?
View Replies!
View Related
Quick Auto-Numbering
Auto-Numbering just an example:- 56 57 58 59 60 The Column above is the first column on a selected sheet. i will select 56 and from there (End-Shift+Down arrow) which selects all the values from 56-60... My question is from here on if there is a shortcut key or 'vba macro' that can autonumber from 1. Thus giving output result of.. 1 2 3 4 5 i want to record the solution for above problem in a macro recorder for different numbers that is why i have to do (End-Shift+Down arrow)
View Replies!
View Related
Automatic Numbering In VBA Etc
I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems... 1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately. 2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them? 3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?
View Replies!
View Related
Numbering Blocks Of Data.
I have 250000 lines of data and at the moment they are in seperate blocks of different sizes, and seperated by 5 blank lines. For Example 112 1523 523 1523 *5 BLANK LINES* 12 23 *5 BLANK LINES* 344 4563 etc. What I would like to do is give each block a number. 1 112 1 1523 1 523 1 1523 *5 BLANK LINES* 2 12 2 23 *5 BLANK LINES* 3 344 3 4563 The lines in between will come out eventually I just need them there as they are difineing the blocks of data.
View Replies!
View Related
Duplicating A Row And Numbering?
I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150. So in the end it would go from: (sorry for the periods.. extra spacing didn't work!) A........B................C.......D AAA...123 Street...City...<blank> BBB...456 Street...City...<blank> CCC...789 Street...City...<blank> To: A........B................C.......D AAA...123 Street...City...1 AAA...123 Street...City...2 AAA...123 Street...City...3~ AAA...123 Street...City...150 BBB...456 Street...City...1 BBB...456 Street...City...2 BBB...456 Street...City...3~ BBB...456 Street...City...150 CCC...789 Street...City...1 CCC...789 Street...City...2 CCC...789 Street...City...3~ CCC...789 Street...City...150 I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.
View Replies!
View Related
Automatic Sheet Numbering
I have a report blank that is comprised of numerous excel worksheets (fixed letter size). During the completion of the report, one may add, delete, and/or move worksheets. I want each worksheet to have a cell that dispalys 'page # of total number of sheets'. Is there a way to automatically update this information?
View Replies!
View Related
Sequential Numbering Macro
I need a macro that will number a cell (A1 for example) starting with the number 1, and another cell (A2) with the number 2, then back to the first cell with 3, then back to the latter cell with 4 and so on.
View Replies!
View Related
Auto Numbering Cell
I've Created a workbook with 30 sheets, and i want to make auto numbering for each sheet . Ex: if i put in sheet "1". cell"A1" = 100 the sheet "2". cell "A1" = 101 sheet "3". cell "A1" = 102 and so on ...
View Replies!
View Related
Numbering Copies While Printing
I have a label which I print from excel and I print multiple copies of the same label. I need the number of copies printed on the label also such as 1/20 ,2/20. I found a good macro on this site but i can't get it print 1 of 2, 2 of 2. Can anyone help me? Sub PrintMany() Dim i As Long For i = 1 To 20 'change 20 to number needed Range("A1").Value = i ActiveSheet.PrintOut Next i End Sub
View Replies!
View Related
Group Numbering In Steps
I am trying to produce a list of numbers (preferred Sheets Function and not VBA). Every group should be of 5 Identical Numbers - starting with 2 and step of 2. (An Example is shown in the attached picture). I would like to produce that list with the help of ROW() and all available build-in Functions. If possible, I will appreciate very much some explanation as for the principal of producing such a list when, for inatance, the step is 3 , starting from 3, while every group includes only 4 identical numbers and the first cell, to present the first value, is A11 and down to A26 - presented here in Horizontal layout: (3,3,3,3,6,6,6,6,9,9,9,9,12,12,12,12)
View Replies!
View Related
Auto Numbering Rows
I have a requirement where, in one of the column i would like to have an auto numbering (similar to Microsoft access). I know this can be done using Macros, but is there any other better alternative.
View Replies!
View Related
Numbering And Sorting The Data
I have a excel file which looks like the following:............. The file contains more than 25,000 rows. What I need is, I have to give numbering to the above including parent id no as well. The nesting level can go to a maximum of six. The result should be 1 Electronics 0 2 Sound & Radio 1 3 MP3 & Portable Audio 2 4 Mp3 Players 3 5 IPod 4 6 Nano 5 7 5GB 5 8 10 GB 5
View Replies!
View Related
Unique Numbering Of Pages When Printed
Would it be possible to have a unique number appear on every indivually printed sheet? My worksheet in Excel fits onto one sheet of paper and I would like a unique number to appear whenever I print, even after closing system and re-opening file at a later date (the last printed sheet is 'remembered' so the next printed sheet is the next number in sequence). So the first time I print a batch of say 100 I would have 100 x identical pages EACH with a unique number, i.e., '0001' to '0100'. The next time I print another 50 say, I would have '0101' to '0150' and so on.
View Replies!
View Related
Automatic Unique Numbering In MsgBox
After entering some values in a form I want to get a unique id for that record to be displayed in a message box and thereafter inserted into column A in the same row as the rest of the information. The numbering should be 1, 2, 3 etc and when I use the application the next time the id displayed should be the next number. This is what I've got so far, but now my unique id is always 1 (not very unique at all). Private Sub UserForm1_Initialize() Dim rIds As Range Dim MaxId As Long Set rIds = .Range(Cells(A, A), Cells(Rows.Count, 1).End(xlUp)) MaxId = Application.WorksheetFunction.Max(rIds) With Me.IdBox.Value = MaxId Private Sub DateBox_Change() DateBox = Format(Date, "yy/mm/dd") End Sub
View Replies!
View Related
Auto Numbering And Workbook Log
I want to create a template in Excel for a change order system. Every time I have a new change order I want it to be numbered. I want Excel to automatically keep a log of all the changes orders to date with change order number, date, title, etc.
View Replies!
View Related
|
TRACKER
