Sum Selected Columns For Single Row
Aug 13, 2013
I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.
I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.
My most recent iteration is:
=SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))
[Code] .........
View 2 Replies
ADVERTISEMENT
Mar 9, 2014
I have an XY plot where the source X value data is a series of time values, each associated with a descriptive text cell. I would like to select a single point (obviously by mouse) and then execute a macro (right click preferred but a separate command button would be a suitable substitute for now) that will extract the X-value. I then plan to use that value to find within the source data the associated descriptive text cell and make a data label from that text. No clue where to start...the macro recorder is woefully deficient in this area.
View 2 Replies
View Related
Jul 7, 2009
I'm creating an excel spreadsheet that will be sent out to a few users to enter data into. In this spreadsheet, multiple columns contain drop down lists created using the Data Validation tool. The lists are referencing named ranges on a second worksheet.
In these drop down lists are terms that consists of one or two words. What I'm trying to do is when a user goes to select an option from the drop down list, I want the matching one letter code to be entered into the cell instead. For example,
Column AK has drop down list with following options:
Good
Moderate
Poor
When a user selects one of the options, I want one the following respective letter to appear instead......
View 2 Replies
View Related
Aug 1, 2014
How can I update column B for each item selected in a Listbox populated as below:
Code:
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i
For each item selected in Listbox1, I want Column B to show "CONFIRMED"
View 3 Replies
View Related
Feb 25, 2009
married, widows, single checkbox is selected
macro solution
good work.
View 5 Replies
View Related
Apr 21, 2014
I want to import one worksheet from different workbooks to Master workbook. In the source workbook, I need to import only ' Code' sheet and in this code sheet , I need only 4 columns(A, E, F, and I ) to import. I also need cell C3and E3 in source worksheet to master workbook
I want to import like 'Code(1), Code(2), Code(3),, and so on. I don't want to import whole thing in the one sheet in the master workbook.
IN the master workbook, the header is on A3, so starting row will be row 4 in master workbook.
Codes below is for 'stacked' style, I guess.
[Code] .....
View 8 Replies
View Related
Aug 12, 2014
I'm prompting the user for what two ranges they want to keep in a excel sheet and then I want to delete the rest of the columns. There may be 5 total columns and there may be 30, it will vary. The reason I want to do this is because I will then save data to CSV file and it can only have two columns of data to be passed on for other data processing.
View 5 Replies
View Related
Nov 6, 2013
I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?
View 6 Replies
View Related
Jun 21, 2013
Daily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.
I have the file with various columns, such are.
Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name
The above column order are not constant, daily the order will changes. But we have to make the order as below
Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid
Like this we have so many columns in the file and we used to rearrange as required.
Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.
View 2 Replies
View Related
Jul 13, 2009
My boss wants me to create a macro to hide columns if there is a "X" on top of the column labels. The macro should be in toggle mode whereby the next pressing of the macro will unhide the hidden column.
There are more than 50 columns in my actual worksheet. The macro has to check column by column whether there is "X" marked on top and hide it if so.
The next pressing of the macro should then unhide all the hidden columns.
View 12 Replies
View Related
Aug 24, 2009
OK, I want to run a macro that will:
(1) go in to sheets 'A', 'B' and 'C'
(2) find all cells in row 1 that equal "1"
(3) copy the entire column and paste as values
View 13 Replies
View Related
Apr 27, 2013
I have a worksheet which contain a lot of data in it. I want only certain rows and columns to be printed when I click a button. Actually I am trying to print a report.
Suppose I have data across A10:M100. Lets say from columns A to D are permanent, if the cell colour of any cell from E to M is not Red then that is to be printed along with the contents in A to D. Since I have bunch of data in that sheet almost 10000 rows I separated data by giving them name. If it is possible to have an option to choose from before printing that would be much much better. Because even though there are many cells left uncoloured I don't want them to be in the print as they are belong to another table (I have given name for each table)
If all columns from E to M in row 20 are Red then that row should not appear in the print. If J20 is left uncoloured then it has to be printed along with the data from A20 to D20 ( which I said permanent). This colour I fill by a macro as when required only for the range E to M.
View 5 Replies
View Related
Dec 6, 2008
I would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.
Or is there perhaps a faster way than using a macro?
View 9 Replies
View Related
Jan 7, 2014
how do I transform thousands of groups of columns (each group has 6 columns) into A Single row (placed into another sheet) using Macro?
Since my data is large, so efficiency is also important.
View 5 Replies
View Related
Feb 12, 2014
Before
XYZ1
XYZ2
XYZ3
XYZ4
XYZ5
XYZ6
XYZ7
After
XXXXXXXYYYYYYYZZZZZZZ1234567
[Code] .....
View 10 Replies
View Related
Mar 5, 2013
I have 100 columns in 1 worksheet, each column has 200 rows, I want to combine all these columns into a single column.
Therefore, Column 101 or a new worksheet Column 1, will have 20000 names.
View 2 Replies
View Related
Aug 17, 2006
I have a long list of 2 columns containing data as follows:
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3
which I would need to move to get one column per country with the top cell the country name and below each name all the related dates, e.g.
country1 country2 country3 country4
date1-1 date2-1 date3-1 date4-1 etc.
date2-1 date2-2
date3-1
I have searched and found almost similar questions and tried to apply it to this case, but not being versed in VBA it was a failure on my part
View 4 Replies
View Related
Apr 27, 2008
I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached)
Raw data in columns A,B,C,D,E as below
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
Converted should be
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2
View 7 Replies
View Related
Feb 14, 2014
When I select multiple cells horizontally at the top are highlighted columns for those cells.
I would need to know the number of those columns (es. 5), "on the fly", without having to rely on eye every time. This number maybe displayed in a status bar or other window...
View 4 Replies
View Related
Apr 27, 2014
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select".
2) Is there a way to select the workbook from were to copy the column without needing it to actually open?
3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
View 2 Replies
View Related
Jan 18, 2014
I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.
I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...
So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..
I made my own small code but dont know how to make it act differently on selected columns....
I would select the entire matrix and then want the macro to run differently, taking into consideration the column names
[Code].....
I also need to add a vlookup formula for some column based on the column B's value...
Ex: This is for Col C
[Code] .....
So how do I write the code in such a way that the code acts on its own differently.
View 14 Replies
View Related
Nov 16, 2013
How can I adapt the following code to only show Columns; A, B, E, F and J from Sheet(3) into a Listbox called lbx_LiveAllocations?
As it stands, this code is only adding Column A. My only alternative has been to add all columns on the worksheet to the listbox, however there's a lot of unneeded information between.
Code:
Dim LR As Long
Dim ctrl As Object
Dim i As Long
Dim dic As Object
Dim arr As Variant
Set dic = CreateObject("Scripting.Dictionary")
[Code] ........
Erase arr
View 2 Replies
View Related
Jun 21, 2013
My starting point is a big data set that needed to be validated by several users. I split the data set into smaller sets that were sent out. All users have filled in the same column called "Input"
What I would like to be able to do is to paste their input into a single column on my original data set and keep the name of the input next to it. Is it possible to paste without overlapping prexisting data ? Something of the likes of F5 ("Select blank cells/text") ?
I've attached a simple file with dummy data to explain myself.
View 5 Replies
View Related
Jun 2, 2014
I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.
Now, I need adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".
I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.
View 9 Replies
View Related
Jun 7, 2014
I am having data in two columns (A & B ). Now I want to get the data in the single column(column C) . Here column A(Name) & Column B(Mobile No). it is something like below.
Here is my input data:
I want to get output results like below in the column C
View 7 Replies
View Related
Dec 6, 2007
How can I sort a single column of data to multiple columns of data. e.g. I want to convert ~1800 rows of data from 1 column to 3 or 4 columns.
View 14 Replies
View Related
Nov 22, 2013
CartonNo1
ProductNo1
LotNo1
LotNo2
CartonNo2
ProductNo1
LotNo3
LotNo4
In the above (repeating for say, 50 or more cartons), is there a way with a macro(I know nothing about) or pivot table(see macro) to get the following format/result?
CartonNo1
ProductNo1
LotNo1
CartonNo1
ProductNo1
LotNo2
[code].....
View 6 Replies
View Related
Feb 27, 2007
I have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.
I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.
Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.
View 3 Replies
View Related
Jan 17, 2008
How can you move data from multiple columns into a single column? I have attached a short example of the data I receive. (In reality, the data could be hundreds of columns and/or hundreds of rows, but this is representative of what I might receive). It does not need to be sorted and including the header row in its own column is optional. I do not know VBA all that well but can muddle through it if someone can give me an example of what the code should look like.
View 5 Replies
View Related
May 20, 2008
I have two excel sheets exported from two sources of information. Both sheets contain 1 column which is the primary data, ie, there is no duplicate of that information. Each information in these column represent 1 device in the real world.
Now, for example:
In one sheet, I have: SDV0620B
In the other sheet, I have: SSDV-B0620B
Please note that, both data actually represent the same device. What I now need to make sure is that, I ccan identify each corresponding data from the two sheets.
So, here is the problem that I am facing, identifying two data which are same but has two different representation techniques. (since the data are from two different sources)
I came up with the idea that, if I can check the sequence of characters with one column to another, then I might be able to pull it off.
For example,
we take SDV0620B, and match the sequence of character with SSDV-B0620B.
therefore, first is S (from SDV0620B)
match with SSDV-B0620B
then, D
match SSDV-B0620B
then V
match SSDV-B0620B
and so on, until the end.
And if the 1st sequence is found in the second sequnce of characters, then it is called a match! Hope I have explained it clearly. I have provided a file with some of the data copy and pasted out, to give you an idea of what the values look like.
View 9 Replies
View Related
