I have a sheet in excel 2013
In column "Y" I want to add a checkBoxs activeX from cell 6 until cell 500
I don't want to repeat it 500 times )":
This is my code for a single checkBox
[Code] ...........
I'm using Excel 2007.
I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
I bought a Surface 2 tablet. When I go to "settings" in the Trust Center it does not show any option to enable Active X.
View 3 Replies View RelatedIs it possible to perform average function on subsequent group of rows and make a new set of rows ?
For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....
Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.
Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12
I've got general ledger information that I export out of my accounting software (see attached spreadsheet). From there, in another spreadsheet I do vlookup formulas to get information from this general ledger. However, in order for the vlookup formulas to work properly in the other spreadsheets, I have to go through this general ledger spreadsheet and manually enter just the first five numerical digits in column A for each Total row. I would like to be able to find a solution that would return just the first five characters of the category (column B found at the top of each section) into the cell in column A on each total row. I usually have to manually enter 50-100 of these many, many times a month so it gets time consuming after a while. I'm using Excel 2013.
View 1 Replies View RelatedI'm back working on my estimate sheet again and hit another roadblock. I have a series of rows all separated by multiple spaces and would like to copy every one to the first empty column on a separate page sequentially until a certain condition is met (first time row starts with zero in column U, in this pic second row down would end routine).
The first column here is U on sheet "Partitions & Woodwork" so since this first row doesn't start with a zero, U10 - BC10 would need to be copied and pasted transposed into the first open column on sheet "Rebirth" (2nd pic below).
U V W X Y Z
It would be pasted transposed here from B2 downwards on sheet named "Rebirth". The next row that didn't start with zero would be pasted transposed starting at c2 and so on until the first time a row beginning at column U on sheet "Partitions & Woodwork" began with a zero (0).
The number of spaces between rows being copied varies on the partitions & woodwork sheet but the columns (U - BC) are a constant every time a row needs to be copied.
How can i hide and unhide column and rows using checkbox in the excel.
detail price qty amount
chair 1000 1 1000
some time i just want to see only the amount or some time i want to see the qty, how can i hide and unhide row or Column
I am trying to create a workbook where the first worksheet has a list of Checkboxes and each checkbox 'unhides' or 'hides' subsequent worksheets within the workbook, depending on whether the box is checked. I can make the checkbox, I can record the macro.
Sub Test_checkbox()
' Test_checkbox Macro
Sheets("Dist").Visible = True
End Sub
How do I link them together? Ideally, I'd like to have my boss be able to 'play' with which sheets are visible (check, uncheck, check, uncheck, etc) .
I've done a check box on excel (ActiveX) which then enters True or False when I tick or untick it. Is there any way which you can change the True/False words to something else?
For example, if the box was ticked it would enter "Electric" instead and "N/A" if the box was unticked?
I have a sheet with many active x check boxes already created but with visibility off. I want code that turns on the visibility, repositions, and checks certain check boxes whose name is based on values in an array named configs.
Here is the code with the problem line indicated:
[Code] ........
The repositioning and visibility work fine. I get an "object doesn't support property or method" error on the indicated line. What is the proper syntax to do this?
I'm trying ActiveX controls for the first time.
I need my code to start at the cell that the checkbox is linked to, but I don't know how to do that.
I have several checkboxes on the same sheet. I already have the checkboxes linked to cells, I just need to know how to tell the vba to find the right place to start.
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
I am using the code below in Excel 2013.
Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then
[Code]....
This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
In a large table what is the simplest way to delete all empty rows? Excel 2013.
View 13 Replies View RelatedIn the first sheet called WSZYSTKIE (All) I input new invoices with the deadline for payment. Everyday I'm looking for invoices that I have to pay today. Dates with deadline are one column(E), dates when I paid is another one(F). I'm wondering if it is possible to do following thing:
After opening file, this would be done automatically: If there is invoice (row) to be paid in next 7 days (great if I could choose what time range I'm interested with), copy entire row to the second sheet called: Do zapłacenia (TO PAY). If there is invoice with deadline which is i.e. 2 days ago, copy whole row to same sheet and mark it RED. If it will be paid, I'm entering date at which I've paid and it should be moved to the next sheet called: Archiwum(PAID), and this row in sheet Wszystkie(ALL_ should be actualized with the date I made payment.
Excel 2013, but finally it will be used on excel 2007.
I am trying to have a checkbox that will hide any row between 6 and 300 say IF column P of that row that is empty, or does not contain the letter "Y", whichever is easier. I have found a bunch of different code snippets, but most hide a predetermined row range, and I need this to be dynamic.
If it can also expand the range automatically as I add more rows to the spreadsheet, that would be even better, but not necessarily required.
My and a work college needed to combine our separate excel worksheets into a single document.
Office 2013 didn't have a function to "import sheet from file" so we used open office to import my .xlsx worksheets.
After we finished importing we exported the final workbook as .xls (so I could open it).
After opening the workbook on my pc (excel 2013) i notice some of the sheets no longer have column headers, but the row headings still exists. (No ABCD, only 1234)
Also I am unable to use features such as "Freeze Pane"
I suspect this was caused by importing and exporting through open office?
I have some nominal data that I'd like to get into a pivot table (Excel 2013). For simplicity let's say it's a one-question survey with 6 respondents:
Q1: Dogs are better than Cats
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Responses:
Agree
Strongly Agree
Strongly Agree
Strongly Agree
Agree
Strongly Agree
I can create a pivot table with this data and get the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Grand Total
6
This all works nicely, however I require that the other options ("Neither Agree or Disagree", "Disagree", and "Strongly Disagree") be present, even if their values are 0, like the following:
Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Neither Agree or Disagree
0
Disagree
0
Strongly Disagree
0
Grand Total
6
What I tried doing was adding a new column and calling it something like Ratings with the following:
Ratings
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree
Then I set the Ratings column in the "Rows" section of the pivot table and the Count of Q1 column in the Values section. This is what happened:
Row Labels
Count of Q1
Strongly Agree
1
Agree
1
Neither Agree or Disagree
1
Disagree
1
Strongly Disagree
1
Grand Total
6
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
long story short, we ended up with an excel sheet like this: Screen_Shot_2014-05-11_at_4.png
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.)
Screen_Shot_2014-05-11_at_46FDQO.png
I googled for days, i only found Visual basic commands i guess? that only merge same rows. and they were poorly made. beside that it didn't work properly. Method to do it automatically?
EXCEL 2013
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
Excel sheet like this:
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.) Any method to do it automatically?
EXCEL 2013
I am trying to auto fill this formula down a column but it doesn't keep the C4,D4,E4...ETC to stay constant
I manually did these two correct ones
=SUM(C5*C4+D5*D4+E5*E4+F5*F4+G5*G4+H5*H4+I5*I4+J5*J4+K5*K4+L5*L4+M5*M4+N5*N4
+O5*O4+P5*P4+Q5*Q4+R5*R4+S5*S4+T5*T4+U5*U4+V5*V4+W5*W4+X5*X4+Y5*Y4+Z5*Z4+AA5*AA4)
=SUM(C6*C4+D6*D4+E6*E4+F6*F4+G6*G4+H6*H4+I6*I4+J6*J4+K6*K4+L6*L4+M6*M4+N6*N4
+O6*O4+P6*P4+Q6*Q4+R6*R4+S6*S4+T6*T4+U6*U4+V6*V4+W6*W4+X6*X4+Y6*Y4+Z6*Z4+AA6*AA4)
when I drag it down it incorrectly looks like this:
=SUM(C7*C6+D7*D6+E7*E6+F7*F6+G7*G6+H7*H6+I7*I6+J7*J6+K7*K6+L7*L6+M7*M6+N7*N6
+O7*O6+P7*P6+Q7*Q6+R7*R6+S7*S6+T7*T6+U7*U6+V7*V6+W7*W6+X7*X6+Y7*Y6+Z7*Z6+AA7*AA6)
I want C6,D6,E6 to be C4,D4,E4 ETC
I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
I've got a column of client company names and because it is based from accounts, the companies names appear many times. I just need to populate a new column with unique names, so I have found a formula to do this:
=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
However when I do this, I CtrlShiftEnter and drag to copy that down, and it's just a copy of the original list - client names are appearing on my new list multiple times. I don't see how this doesn't work... I'm using 2013.
I have an Excel file in which I want to find the numbers which are repeated in several columns (B to L), but there are too many rows to find them looking one by one cell, is there a function which will allow me to find the common values which appear in these columns?
eg.:
A B C
1 3 4
2 1 7
2 4 7
4 1 1
results: 1 & 4
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
Is there a Column Filter Drop down Keyboard Shortcut for Excel 2013?
In Excel 2013, is there a keyboard shortcut to access the column filter drop down. For example, if you are on the cell A1, and you select Filter under the data menu and you want to filter column A without using your mouse, is there a keyboard shortcut to do this. I know Alt-A-C, clears the filters, but I want to know if there is a shortcut to access the filters in the column. In case my explanation isn't clear, I have included some screenshot pictures of the filter, before and after it is selected, to show what I am talking about, and what I am trying to accomplish without the mouse.
column filter.PNG
Filter Dropdown.PNG
I was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
View 1 Replies View RelatedCurrently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
