Making A Calculation On A List Of Data?
May 14, 2014
In it I have a list of different profiles (From autocad Civil 3D). With a column for the station value, and a column with the elevation. These profiles are arranged all below each other. In the example xls, I've limited it to 2 example profiles to make it a bit clear. The 1st few columns come from the export out of Civil 3D (with empty rows between them, but these are easy to delete). For each station of the profile, you have an elevation
What I need to do is calculate a 3D length for each profile. So manually you can do this with A=(B2+C2)^1/2, and then make a sum. Which is not really a problem if you have a list with 2 profiles. But sometimes we have a list with 100 profiles.
Optionally, it would also be handy that we then get a list in another sheet, with in 1 column the profile names, and next to it the 3D length.
My main question is: Is this possible with VBA? (I would assume it is..) Next, it would be handy if I could be directed in the right direction to produce these routines, because I don't have that many experience in vba.
View 3 Replies
ADVERTISEMENT
Nov 10, 2008
1. Is it possible for this hyperlink box to only be displayed when ALL the questions have been answered? i.e. All drop down boxes have been used?
2. Is it possible for the result (along with their individual names and scores) to be saved on another sheet? This of course would be supplemental (i.e. When the next person finishes the test, their name would be next on the summary etc.)? I would then have a whole list of who had taken the test...when...and what their score was!
3. Finally, and I know I'm pushing my luck, but is it possible for this summary sheet of names and scores to be hidden?
View 9 Replies
View Related
Jan 14, 2009
I have a workbook of contracts that I have designed in excel. Each worksheet tab/title is the contract number, i.e. 3487. On each individual contract I have the contact name, address etc. Each cell/field is in the same position on every contract. In other words, every B1 is the Group name, every B2 is the first name, every C2 is the last name, etc. What I'm trying to do is to make a new page with all of the contact info on it so I can make labels etc. I hope this is clear.
View 5 Replies
View Related
Dec 12, 2007
i have to make a synonym list out of existing wordlist for my professor. As far that’s the most boring job I would like to ask you people, if it could be automatically done by a vba code. I don’t think that that’s technically possible but I hope though that I am wrong and that some one here can help me. I will try my best to describe the problem as good as I can. Please let me know if its not precise.
Here we go!
I have a wordlist containing four columns A,B,C and D. In column A we have got Source Words(sw), in column B word specifications1(ws1), in column C word specifications 2(ws2) and in column D we have many meanings and target words separated by comma (tw1,tw2,tw3,…) for (sw). The interesting columns for our problem are only A containing words (sw) and column D containing meanings and synonyms(tw). The wordlist is supposed be get larger through finding, enhancing and adding synonyms in the same list. The difficult thing would be to make a macro with two modes. In first mode which asks each time before it adds or enhance a cell and in second mode the macro should do all the work alone without asking the user. The existing wordlist looks like: ...
View 41 Replies
View Related
Apr 2, 2007
what im trying to do right now is get some scripting to work better
the following is what the macro recorder gave us
what i need it to do is read how many rows are in the range and put the totals row one after the end. now it seems to be set to 507 and one wksheet has almost 700. can someone help me decipher all this.
thanks
kev
Sub General_Reports()
'
Range("A:D,F:F,G:G,H:H").Select
Range("H1").Activate
ActiveWindow.SmallScroll ToRight:=4
Range("A:D,F:F,G:G,H:H,I:I").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
View 9 Replies
View Related
Sep 18, 2013
I have a bunch of excel files in a folder, and I want to make an excel spreadsheet that makes a list of them all. This drive is a shared drive and their are different authors for files. I have already made the list, with the file name, date modified, date created, etc. However, I am having trouble importing the list of the authors for the files. Is there a way to automatically import a list of excel files in an excel, with an "authors" column? I can't do it manually, for their are hundreds of excel files.
View 1 Replies
View Related
Jul 22, 2009
I have one column with a list of 2000 rows like this
apple
banana
carrot
dog
elephant
fish
goat
hat
I want to do is put all those lines of text / rows into one cell with the word "or" between each phrase. In other words in one cell all the above should be made into "apple or banana or carrot or dog or elephant or fish or goat or hat" . There should be no breaks.
View 2 Replies
View Related
Sep 25, 2011
I have a list that looks sort of like this:
Last Name | First Name | Date | Service Day
Smith...........John....................Monday
Jones............Alex...................Thursday
etc..
This list goes on down for like 50 cells. Anyway, I'd like to make a new list based on Service Day so that I can organize the names under the service day so it should look something like this:
Monday | Tuesday | Wednesday | Thursday | etc..
Smith, John..............................Jones, Alex
etc...
I tried using the vlookup function but then it confused me when i also needed to match the names with their respective service days.
View 7 Replies
View Related
Jun 26, 2013
I have a table where I need to make a drop down list in two consecutive columns (G and H). The list in column H will depend on the Option chosen in Column H.
Column G
Compensation
Payroll
Column H
COMPENSATION
Savings Box
Update Information General PAYROLL
Payment Inquiry Update Information Severance Pay
As shown above If you select Compensation in G you will have those three options in column H and the same for Payroll.
View 2 Replies
View Related
Mar 27, 2014
How do i make a drop down list in a cell show Select One in the cell when the cells previous contence (Option selected from drop down list) have been deleted. E.g Cell E5:E400 must show Select One in the cell and when clicked on show drop down list I dont want a input message box or error box i can do that with validation
View 1 Replies
View Related
Jul 14, 2014
I need to make an automated way of extracting a list of IDs and the associated cost by a date range, say 6/1/2015 to 12/31/2015, plus the starting letter of the ID. I need the data to then fill a form where it can be sub-totaled. There are several additional columns of data that are not relevant so I know I can create a pivot table and then filter the data, but then I need to copy and paste the filtered data into the form. Is there a way to do this without me copy and pasting everytime?
I added a test worksheet to show the data and form.
View 6 Replies
View Related
Jul 4, 2014
I added an attachment with an example. You will see a matrix, with in the most left column an ID, then the headers of the matrix has different numbers that mean something.
What I want to do is, you fill in a number in the combobox, then press on a button and a report will be made on a new worksheet. With a list of the numbers and dates in the matrix that the ID has. Like shown in the example sheet in the workbook.
View 1 Replies
View Related
Feb 23, 2010
I've been using the function =vlookup(...) to fill up the cells in my spreadsheet. However, a problem I've come across is that vlookup does not make the values in the cell permanent and keeps drawing whatever value is found in the corresponding cells it searches. Thus if the file it draws it's info from goes missing or is edited, my data also changes accordingly. Therefore I would like to ask if there was a way in which I can make the data permanent in my spreadsheet.
View 2 Replies
View Related
May 26, 2014
I have a drop down list that shows phrases for different math functions, like "divide by 2," "divide by 4," divide by 8," etc. When one of these items is selected, I want it to perform that specific calculation on a range of nearby cells.
However, I also want the user to be able to see the original value of the cells if they select "show original value" from the drop down list.
I have attached a workbook as an example. I think macro would probably work best.
oz_list_math.xlsx
View 5 Replies
View Related
Jul 1, 2014
I have a big system of data, me not knowing better i have it such that the categories is listed in the start of the row, and new data for periods are listed in the columns following. when i mark the data and click table, it puts the scroll down thing on top of the columns, which is of no use to me at all, i need them at the start of the row. is this possible, or do i need to reorganize all the data, and VBA code used to sort and collect it ?
View 7 Replies
View Related
Aug 7, 2014
I want to take some data which is dynamic and make it static elsewhere in a workbook without doing a manual copy of the said data. Is this even possible?
For example, let's say Sheet1!A1 is the dynamic data I am using, and on Sheet2!A1 I want to replicate this data, I know I can just reference Sheet1!A1. However, this would make Sheet2!A1 dynamic, and I want it to be static. I know I can do this be just typing it in manually but I have a lot of data I would need to do this for, is there some way of making Sheet2!A2 static whilst still referencing Sheet1!A1?
View 2 Replies
View Related
Jul 26, 2006
I'm trying to make a chart (which will also be on the UserForm) from data that a user will enter onto a UserForm. The problem is that I am trying to select a data range as one of the charts 'series' but I can't quite find the right code. At the moment it looks like this:
ActiveChart.SeriesCollection(1).Values = Range(BMIM20Ttl, BMIM2025Ttl, BMIM2530Ttl, BMIM3040Ttl, BMIM40Ttl)
ActiveChart.SeriesCollection(1).Name = " Total"
ActiveChart.SeriesCollection(2).Values = Range(BMIM2075, BMIM202575, ...
The problem is with the Range function.
View 9 Replies
View Related
Oct 30, 2013
I'm currently trying to set up a sheet ...
When setting up a Direct Debit There are 3 options, the 7th, 17th and 27th. I've put these into a drop down list. I need this list to be slightly dynamic. I must allow 10 working days from the date of the arrangment before I can apply the DD.
To explain further. If someone were to want to set up a DD and spoke to me today. If they then requested the DD be taken on the 7th, I wouldn't be able to set it up until the 7th of December as 10 working days are required. I'd like the list to reflect this. I can do it in OO with a simple if / switch but excel is a strange creature and I'm not sure how to approach the problem.
From that I think I also need to calculate the number of weeks remaining in the current financial year. I think I should be able to do this by using the Today() function and some maths.
View 1 Replies
View Related
Mar 6, 2010
I want to take data from an Excel 2003 Worksheet as shown below:...
View 9 Replies
View Related
Oct 15, 2007
I have a data validation list in cell D11 on sheet "Data Entry" and a command button "btnMultipleProperties" that I only want visible if "Multiple" is selected in "D11" I have the below code in "This Workbook" in VBE but it doesn't work. What did I miss?
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Data Entry")
If [D11].Value "Multiple" Then
btnMultipleProperties.Visible = False
Else: btnMultipleProperties.Visible = True
End If
End With
End Sub
View 8 Replies
View Related
Sep 6, 2006
I have a chart that shows up to a list of 28 people and the number of sales for that day. I'm try to make the chart only show the names and number of salesperson that are not = to null. This is what the chart has for values right now "=' Nest Average'!$C$6:$C$33". How can I make it so that it only shows those cells if not = to null.
View 2 Replies
View Related
Jun 23, 2013
I have a table with 500 entries which is some physical properties for some chemicals. What I am needing to do is get something to find out what chemical formula the user has typed in another cell on a separate sheet, then VBA finds the component on this table and picks out information from columns within that row. Some of that data will then be used to do a calculation. The user can select up to 20 components, so VBA needs to be able to do it for 1 or anything in between 20 components. The data which I need to use for further processing is within columns D to J.
View 1 Replies
View Related
Jul 11, 2014
I have got historical intraday-data of a stock index and first of all need to summarize them by seconds: The data looks like that:
time price
08.00.00 159
08.00.00 153
08.00.00 148
08.00.01 157
08.00.01 152
08.00.02 167
08.00.02 151
08.00.03 155
08.00.03 154
08.00.03 154
So I want to calculate the average price per second. Is there any formula to tell Excel that it should sort it by second and further calculate the average price of it.
View 5 Replies
View Related
Jan 27, 2009
Im measuring arrivals of buses during a two hour period, for example 10 til 12 Within in this time i have 12 buses.
The Problem
What i need to do is calculate how many buses have turned up in a "rolling hour". This means taking a sample of any 60 mins within the two hours and calculating how many buses turn up. There needs to be a min of 6 buses in that 60 mins
In theoy there would be 60 "rolling hours" within a two hour period.
For example first 60 min is 10 till 11, second 60 min is 10:01 till 11:01 and so on.
View 8 Replies
View Related
Oct 3, 2006
i am trying to find the formula which will calculate information that is between two dates. for example i have the dates Jan'05 right through to Dec'06 and in each month i have different values eg, Jan is 5, Feb is 10 etc. i need to find a formula which will calculate the data between Aug'05 and July'06.
View 8 Replies
View Related
Oct 16, 2009
Suppose you have a large database, which consists of high frequency minutes data, you need to calculate differences in values between each different time intervals, e.g. from 1 min to 10000 mins.
the format is looked like below
1 min aaa
2 min bbb
3 min ccc
4 min ddd
.................
......................
More specifically, what I am supposed to do is to calculate aaa-bbb, bbb-ccc, ccc-ddd, and then aaa-ccc, bbb-ddd, and then aaa-ddd and so on.
I know the VBA has a function to do repeated executions but am not sure how to adjust the time intervals.
View 9 Replies
View Related
Oct 5, 2009
Im trying to work out a function that will calculate the average accross a 3 column array. Obviously there is but I need to make any cell's with 'Free' give a value of 0.
I need some kind of IF("Free",1) type thing, to run before the average calculation, but I dont know how to put it together to make it work, I thought about a Loop, but then how do I make sure it only goes to the next cell in the selection?
View 9 Replies
View Related
Mar 5, 2013
I have an excel spreadsheet that performs a particular calculations using a large set of data. However, I have over 1000 sets of data that need to be feed into this sheet and obtain the output calculation. Obviously I don't want to have to copy and paste in each new set of data to obtain my result as this would take a very long time. I am brand new to VBA, so was wondering if there is a way to write code in VBA that will automatically perform these calculations for one data set, save the output into a cell, and then move to the next data set and so on?
View 4 Replies
View Related
Jun 18, 2013
I need to average data following the format below by average per week by week/store combination e.g. wk 1/store A average is 6.
Week
Store
Sales
1
A
7
[Code] ..
I can easily use SUMIFS to achieve this, but I have a large amount of data between the weeks of 1-52, a dozen different stores and I will be adding to this. I don't want to have to enter new SUMIFS every time I enter a new wk/store combination. How do I get this info to automatically calculate?
View 3 Replies
View Related
Jul 30, 2007
I'm using Excel 2003 with WinXP SP2.
I'm trying to make a spreadsheet to calculate my GPA for my studies. I can get everything to work fine except I want to be able to calculate an adjusted GPA based on a certain value of classes to drop.
The way it would work ideally is that I would be able to search/sort a data set and if it contained "X" number of entries then the "Y" number of lowest marks would be omitted from the AGPA calculation.
I'm having trouble designing a function/formula or scheme to sort the data range, and identify the lowest "Y" number of entries and recalculate the GPA without using them.
View 9 Replies
View Related
