I have a table of numbers to which a new number is added every day. I want to reference the last value in a separate cell. For example, in cell A1 I want to insert a formula that will look at Column B and select the last value. The values in Column B wil be added to each day, ie a new row will be inserted.
I have a code that works fine, however only uses one cell as a trigger. I need each cell in column M to run the code, so at the moment only M6 will trigger, and not M7.
[Code] .....
I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.
I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.
I m trying to make a button to add values to another sheet in my xls. Ive done that... now i try to autofill the percentages from left and above one row....
View 9 Replies View RelatedUsing VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View 9 Replies View Relatedi dont use excel that often, and normally only for really stuff, so apologies, but i couldnt find a solution.
I have a spreadsheet of expenditure, with a column for a category of what was purchased, and then the amounts in the next column, arranged chronologically. eg
01/02/08 food 20.68
06/02/08 petrol 44.65
07/02/08 food 117.65
etc, with various oither columns for week nos (for averages), comments and the like.
Now I would like to do some analysis, for example the mean weekly food spend. I think this should involve checking the category column for "food", and if found, adding the adjacent cell (containing the amount spent) to the sum (and then obviously dividing by number of weeks) - but i cant work out how to do this!
I'm trying to create a formula that searches through a row and selects certain values. I have a list of individuals in rows followed by their 18 scores in columns. What I'm trying to do is only select the best 8 of those scores and add them together. I tried a few things but this is a little over my head.
View 5 Replies View RelatedVBA and selecting non-zero values.. ...
View 9 Replies View RelatedI have a worksheet with about 10,000 rows of data and from this raw data I need to create 60 or so separate spreadsheets for 60 different companies- so around 160 some row actually pertain to a given client. However, it's not fixed and from one month to the next the actual number of rows changes so I can't just use a simple range. There are two possible ways to mark that the data pertains to a new client. But it will be easier to just show a quick example:
1/1/2014
CompanyA
Total
CompanyA
1/1/2014
CompanyB
So anytime it runs into "Total" in column 1 it should know to start another iteration or when the cell in column b does not equal the cell directly above it. Below is a truncated version of what I have so far:
Sub MonthlyStatements()
'
' MonthlyStatements Macro
'
' Keyboard Shortcut: Ctrl+w
'
Dim taxi As String
Range("F32").Select
Workbooks.Add
[Code] ......
I need to put a part of a text from a column and place it in a new collumn. At this moment I use 2 columns and an extra sheet to solve this.
I have a collumn with a lot of data like:aaa-bc
bbb
abc-ba-bd
aa-bb
bbb-xx-yy
aa
abc-yy
klmn-zz
klmn-yy-ff
What I need is behind every cell a new cell with only the 1st part like:aaa
bbb
abc
aa
bbb
aa
abc
klmn
klmn
What I do now is use a new column with this formula:
[Code].....
And I made a translation table in which the 1st 4 characters are translated to what I want to see. In a 3d column I use
[Code]....
to get the results I need. Since I need to get results like this very often this method is very time consuming.
Is there a formula available I can use with only 1 new column?
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received.
I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
I am trying to create a spreadsheet to select different values. I have 5 columns with type A values then 3 cols with type B values, a blk col then 1 cols with type B values and 5 cols with type A values then 1 cols with type B values and 2 cols with type A values a blank 1 col with a B value, 2 cols with A values and 2 cols with B values. [ A A A A A B B B Blank B A A A A A B A A Blank B A A B B ]
I need to select 5 lowest values from this set but a minimum of three A values need to be used. I have used the min, small 2, small 3 for the A set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. I am attaching the formula I started with.
=(MIN(F3:J3,R3:T3,V3:W3))+(SMALL((F3:J3,R3:T3,V3:W3),2))+(SMALL((F3:J3,R3:T3,V3:W3),3))+(IF((MIN(K3: M3,U3)<SMALL((F3:J3,R3:T3,V3:W3),4)),(MIN(K3:M3,U3)),(SMALL((F3:J3,R3:T3,V3:W3),4))))+(IF(SMALL((K3: M3,U3),2)<SMALL((F3:J3,R3:T3,V3:W3),5),SMALL((K3:M3,U3),2),(SMALL((F3:J3,R3:T3,V3:W3),5))))
I have four ranges of data in a worksheet that are sorted in order by the value in the final columns. Based on this value I would like to flag the highest five values from all ranges by adding a value in the adjacent cell. Is there a formula/function that I can use to identify the highest values from a number of data ranges?
View 9 Replies View RelatedI have set of data in excel which is basically a table of workload and I need to be able to select someones name from a dropdown list and all the other names are removed.
I've attached a screen shot of an example, I want to be able to select any name from a dropdown e.g. Select Trevor and then only cells which contain Trevor are shown (i.e. the other names disappear). BUT all the jobs 1-10 and Stage 1-3 have to stay even if Trevor doesn't work on them.
Screen Shot 2013-10-15 at 16.44.12.png
I am trying to select a range of identical values in a column. Basically I need to ask Excel to highlight any and every part of the worksheet where the value '0' (zero) occurs 30 or more consecutive times in any given column.
View 2 Replies View RelatedMy worksheet contains several codes that I then have to lookup in a different worksheet in a different workbook to obtain the relevant data associated with that code. At present I do this by activating the appropriate worksheet, Using Find to locate the appropriate field in that worksheet and then using activecell.offset to get the needed data. This is the only time that these worksheets ever need to be activated so I was wondering if there is a way to do this without having to actually activate the sheet. As an exapmle this is what I currently do:
' variables defined earlier in the program, Tempsheet = workbooks(FileNm & ".xls").worksheet("Temp")
Spark = Cells(TRow, 8).Value
' Activate the reference Sheet
Workbooks("Waveguide Properties.xls").Worksheets("Spark Bends").Activate
Cells.Find(What:=Spark).Activate
COM = ActiveCell.Offset(0, 2).Value
Edge = ActiveCell.Offset(0, 3).Value
SparkArray(i, 1) = ActiveCell.Offset(0, 1).Value
SparkArray(i, 5) = ActiveCell.Offset(0, 4).Value
SparkArray(i, 6) = Spark
' Reactiave the workbook and worksheet I am working in
TempSheet.Activate
I need to select the whole column ABOVE the active cell. Ctrl-Shift-UpArrow is no good because it stops at the first blank cell. And selecting the whole column is no good either because when I subsequently paste into the column, it pastes in all the empty cells of the column, meaning my worksheets expands from a few hundred rows to 1 million!
Shift-Home does what I want on rows. Is there an equivalent for columns?
Cells(xrow, 3).Select --
Is there any way I can select more than one column.
I tried Cells(xrow, 3:10).Select but it gives error.
I wrote a Macro that select info from Worbook "A" to Workbook "B". In that Macro tha columns B:B, E:E and F:F are selected.
The problem is that the layout is not always the same so the correct columns keep shifting positions. The only thing that never changes is the "Column Label" in the first Row.
So, no matter from which column I alway have to copy: "Order Date"; "Status" and "MetofPay".
Is there anyway to tell my macro to look for those columns instead of B:B, E:E and F:F?
I have to correct a spreadsheet that takes a bunch of values and creates an average.
=average(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357)*100%
I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.
So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.
I managed to correct that with this
=averageif(N15:N357;"<>#DIV/0!")*100%
It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.
So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try
=averageif(N15;N29;N43;N57;N71;N85;N99;N113;N127;N141;N155;N169;N183;
N197;N211;N225;N241;N255;N269;N283;N301;N315;N329;N343;N357;"<>#DIV/0!")*100%
It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.
The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
See the attached image to make it clearer..
Excel2010
Excelhelp.jpg
augmenting current code to select the column after a column. Current code is:
Code:
If Sheets(data_sheet1).Cells(2, iCol).Value = "Stations" Then TargetCol = 1
If Sheets(data_sheet1).Cells(2, iCol).Value = "Agency" Then TargetCol = 2
If Sheets(data_sheet1).Cells(2, iCol).Value = "Advertisers" Then TargetCol = 3
If Sheets(data_sheet1).Cells(2, iCol).Value = "Product Code" Then TargetCol = 4
The next line should find the column after "Product Code".
I have created a macro that loops through column 4 and gives the value of 2 other cells acording to what is found.
The problem comes when the macro only works sucecefully if i click on a cell in that column. Therefore i think the macro is not doing this.
Im sure that i have used the right code does anyone know what is wrong.
I am creating a spreadsheet to manage Block ID conflicts when adding mods to a Minecraft Server. Example spreadsheet is attached.
Description: Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).
Issue: The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22).
I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed.
I putting together a spreadsheet that applies Payments (as they come in) against the oldest open invoice. As payments come in, old invoices are closed out. An aging is done and late fees are applied.
I have made a macro that inputs all of the information of the invoice in a list. However, when a payment comes in, I am trying to write a macro that:
1) in column G - Finds the first non-zero balance
2) Applies the payment amount to that open invoice. If there is additional funds left over after the satisfaction of that invoice, I would like the payment to be applied to the next open invoice until all the funds of the payment have been drained.
I am having so much trouble trying to even just locate the first nonzero balance and select it.
I would like to select the next empty cell in columb A after running this macro, what code do I need to add to this to enable that.
View 2 Replies View RelatedI have a worksheet which already has some built in formulas which take the data in columns V and W, and use them to build other coding in column P. The trick is that the coding created in column P will be six times as long as the source data in columns V and W.
I.e., a single row containing "Sample1" and "Sample 2" in columns V and W respectively create the six following rows in column P:
Row 1:
Row 2: TMUnknown
Row 3: tested1
Row 4: Sample1
Row 5: Sample2
Row 6:
I want to be able to select the accurate length of Column P, which should be 6x as long as columns V or W. Any dynamic way to do this? (Since the amount of data pasted into columns V and W will change each time I use this worksheet.) I know how to select set ranges, but not how to adjust them as multiples of the length of another column
I want to select a range of rows 11, 14 and 23 to 24 in column j, plus the 2 rows to the right of column J and run a sum formula on the range. My procedure is selecting rows 11/14/23/24 and doing the sum for only column J. How do I make it so it runs the sum formula on all 3 columns. My code is copied below. I am fairly new to VBA.
Code:
With ActiveSheet.Range(Cells(27, j), Cells(27, j)).Select
Set sumRng = Intersect(Range("15:21, 25:25"), Columns(j)).Offset(0, 2)
ActiveCell.Value = WorksheetFunction.Sum(sumRng)
End With
I am working on a VBA macro, using the following
With wsSheet.Range("A:A")
ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
MyArray = wsSheet.Range("A:A")
For i= 1 To .Rows.Count
For j= 1 To .Columns.Count
wsSheet.Cells(i, j+ .Offset(0, 3).Column) = MyArray(i, j)
Next: Next
I want to select alla values in column A, but when I specify the range as "A:A" the code results in an infinite loop! How can I come around this?