Trying to sum up a column that has an unknown length. This is a canned Excel form that is saved within a software system and is used to create customized documents. Usually, I would enter the table name.field name in a cell to pull the data from various parts of the system.
In this case, that data resides in AA234 of my customized Excel sheet. When the user retrieves this doc from the system, the data could run several rows, starting from AA30 to AA255. So, in my "Total" cell, I've entered =SUM(AA1:AA255), so it will just add up whatever is in the column, regardless of how long it runs upon document generation. When I try to pull the document, I get "=SUM(#REF!)" in that field instead.
I have a column that begins in the same place all of the time (Cell C6) however the length of this column can vary. How can I copy the column using VBA?
I have a data set that contains a repeating cycle. I want excel to find the first and second occurrence of the data crossing the zero axis and then report the time between the zero crossings. The data is not purely in ascending or descending order and often just changes from positive to negative or negative to positive without hitting the actual 0 point.
I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
I have a column of data where the seperate entities are divided by blank rows. for example: i need to calculate sum(G3:G15) then skip the blank row (G16) and calculate the next group, say sum(G17:G50) and export that import to a new worksheet.
The issue is that the column lengths vary from day to day so the blank rows will not be in the same position.
I want to change looks of my workbook with freezing columns on left side, and columns on right side. Between are days of months, where I want to use scrollbar.
I know I can freeze only on one side, but don't know how this Split option really works.
See this screenshot, that's how I want It to look (brown colour for columns that need to be frozen) Untitled2.png
I have to draw a polygon of n sides whose , whose length of side will be given in the cells of excel and the diagram should be displayed in excel itself automatically
Like I have A1=5 A2=5 B1=4 B2=3 C4=5
Then diagram should be like in the attachment : Untitled5.png‎
I'm trying to make a formula but are failing. This is the data:
Cells: a1= ant-gel a2= ant - gel a3 = size 1 - 6 a4 = size 1-6 a5 = book - 5 a6 = book-5 a7 = good book
I want a formula that displays "True" if cell contains a "-" symbol AND the dash symbol is surround on both sides by a number (a space can go between dash and number)
I want the results to go in column B
For example, if a1 contains a dash and dashes are surrounded on both sides by a number, then "TRUE", otherwise "FALSE.
In the attached file, there are three sheets: Data and Private. Sheet Data contains data source. I want to try to covert the data in sheet Data into Private in triangle format.
For example, Sheet Data range A1:A5 will be converted into the latest diagonal in sheet Private range A1:E5. Sheet data range D2:D5 will be converted into the second latest diagonal in sheet Private range A1:E5. Sheet data range G3:G5 will be converted into the third latest diagonal in sheet Private range A1:E5. Just follow the step, there will be a triangle range A1:E5 in Private. Same thing, there will be another triangle in Private.
During the past few years, I use an Offset/Row/Column formula to create a triangle. I feel code can do the same thing because the data structure follows a kind of pattern. I tried and failed.
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
Sheet1, there have data in A1 (100); Sheet2, there have data in A1 (200) and A2 (300); Sheet3, there have data in A1 (400), A2 (500), A3 (600); Sheet4, there have data in A1(700), A2(800), A3(900) and A4(1000).
Now I want to use For Loop code to copy the data in Sheet1 through Sheet4 and paste them in Sheet 5 beginning with A1 into a triangle format:
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If: Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I sent an excel file to a friend. When they opened the file a half triangle mark appears in any cell where there is a formula. How do i get rid of this mark?
Using web queries i get in a cell ie: 3¾ or 1¼ The last part of these "numbers" (3/4, 1/2, 1/4) is text that i want to convert into numbers (0.75, 0.5, 0.25). If i isolate this text in a cell (with the right() formula) the code() formula gives 63 as result for all the above texts. I may solve the problen storing these texts (3/4, 1/2, 1/4) in separate cells (pre-fixed in some cells) and then for my new data (from query) do some search/find . Is there a better way solving this using a different way-macro?
I am using a spreadsheet written by someone else that contains a type of cell entry I am not familiar with. These occur in several tables. The first 3 columns of each table contain numbers or basic formulas. These are followed by two columns in which every entry appears to be the following:
{=TABLE(,B33)}
Despite the same apparent "formula", the number displayed in each of these cells is different. If I click on the formula display box (to the right of the cell address box) to edit the "formula", the brackets disappear.
Can anyone tell me what sort of beast I am dealing with here?
I'm modifying a template that originally shipped with Excel 2003 (I have not upgraded to 2007). In the template, there is a pop-up box (not a dialog box) that shows up when I'm on certain cells. I've attached an image of it. It's the yellow box containing the words "Company Information..." etc. I cannot find any way to remove it! It's not a comment, and selecting it doesn't allow you to edit it. What is it, and does anyone know how to remove it?
I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this
Dim MyCount As Integer Dim r1 As Range 'No of rows in Col D MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count Set r1 = Range(Cells(MyCount, 4))
Is it possible to display a dialog box or msgbox that doesnt have an OK button ?
i.e I want a message that comes up on the screen that says "Links Updating...Please Wait" which then automatically changes to "Links Sucessfully Updated" on completion...I dont want the macro to be interrupted by the msgbox/dialog...
