I am trying to find a formula that will sum the values of two columns with the same heading (in my case the heading is "GST"). Every month this info changes, so the GST may not be in the same column all the time. I have around 1000 rows I need to sum. Example data below:
Amount
GST
Amount Total
Card Fee
GST
Card Fee Total
[Code]...
Is there a formula when I can sum two columns based on the column NAME?
In a gradebook spreadsheet, there is code to add new columns to right of existing columns. Each new column should be included or appended to a particular Named Ranges--named with the same name as the column's heading. Here is existing
Sub mcrNotes()
' This adds a column for student notes, such as daily progress notes.
' The heading for the notes column is "Notes"
' The pre-formatted columns are being copied from a sheet named "Fields".
' The columns are inserted into a sheet named "Grades".after the last data column.
Sheets("Fields").Select
Dim Ndate As Range
Set Ndate = ActiveSheet.Range("E11")
Ndate.Value = Date
Columns("E:E").Select
Selection.Copy
Sheets("Grades").Select
lastcolumn = ActiveSheet.Cells. Find(what:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column.......
Is there any formula out there that will loop through columns and automatically define name ranges. I need some code that will automatically assign Names to the first row of the individual columns and then automatically define the range ie with an offset command.
View 7 Replies View RelatedI am doing a lookup, populating a column (which works although slowly) then (in this part) doing a search on the header row.. If the heading contains the word "Category", I wish to insert a blank column next to (the column containing the heading), colour it yellow then look for the next heading and repeat if found.
It appears to clear my first row so I must have my columns referred incorrectly as rows
Dim FWord As String
Dim i As Integer
Dim lCol As Long
Dim MyString As String
.
.
FWord = "Category"
lCol = Range("A1").End(xlToRight).Column
For i = 1 To lCol
Cells(1, i).Value = MyString '
I want to bring some scattered rows in data range directly underneath the appropriate columns
Here is the description of manual way of doing the same task.
1. first, I check for last empty rows in column "M" i.e for country(By pressing CTRL + Down arrow key in column "M") and then i look into the immediate next empty rows where is the date values, usually i found it into next rows but in a different columns. so i copy the range one cell before from date values till last columns in that same row and i bring it back to the original row where it should be indeed.
For example after pressing CTRL+down arrow key in columns "M", If i see date values In next immediate rows "E7". I copied from "C7" or "B7" (one cell before date value cell) ,depending on In which cell the date value is sitting till last columns i.e "N7" and paste it manually to "D6". and I do repeat the same work manually till i aligned all rows to their proper place.
I am attaching my file also.
I have one worksheet which comes out of an online application (for the purposes of this example I will call it "Online Sheet"). The column headings in this sheets are always named the same but could theoretically appear in any column address. For example, the column header "Completed" could appear in column "X", or "AT", or "ZZ".
On a second sheet (called "Code Sheet") I have to unscramble all of this data into a standardize layout. Each row value has a unique ID which appears on both the "Code Sheet" and the "Online Sheet". However, in the "online sheet" the unique ID could also appear in any column. Like the "Completed" column this column also has a unique column heading ("Unique ID").
Using Match I can calculate the column number in which the "Completed" column appears in this instance of the online data. For example, "Completed" = Column "25". However, I now need to use some lookup function on this column based on the row in which my "Unique ID" appears of the "Online Sheet" (which could of course be in any column in the "Online Sheet").
For example, if "Completed" is in column "25" and the "Unique ID" which I am referencing on the "Code Sheet" appears on row 14 in the "Online Sheet", then the lookup formula must return the value of column 25 row 14 on my "Code Sheet".
Did anyone use the above tool ? Can we use a macro to launch that tool to search for words in a excel cell ?
View 2 Replies View RelatedI uploaded a simplified version of the workbook I'm manipulating for reference. I'm looking for a formula where I can get the ROW heading of a table to be returned based on the max value of a certain column. My column headings in the table are months. I have separate place in the workbook where I have the Months listed in a Column (A9:A......) and in Cells (B9:B....) I want to return the row heading name from the table. The tricky part is that the column headings don't always stay in same place, so Jan isn't always in A1, sometimes it is in B1 or C1 of the table.
Essentially from my months listed is separate part of the workbook. In the cell next to each month I want to match that month with column in the table. In that column find the max value and return the row heading.
I have several sheets (200) with each sheet having its own unique number e.g. 50505, 55005, 55010.... and I have one main sheet called MainSheet with all the sheet names in column A.. What i would like to achieve is to get cell I1 from each sheet to appear next to its sheet name reference.
expected result
A B
50505 Health <-- Appearing from cell I1 of sheet 50505
55005 Pharma <-- Appearing from cell I1 of sheet 55005
I have a 70x70 table where I teased out the largest 20 values. I'd like to utilize a formula to deliver the column headings (as well as the row) headings for each of these 20 values. VLookup and HLookup don't seem to work for me, and Index/Match seems to only work for single columns.
It might be worth mentioning that is highly unlikely that any of the values in the table are repeated.
I have code that copies data from one tab, called "Report" - to another tab called "Master". The data in the "Report" tab is actually 2 reports. Both reports have the exact same format but are separated by 1 or 2 blank rows and a Heading row. Before I run the code, I have to delete the blank rows and middle heading row.
I was wondering if there is code that could find these rows and delete them.
The row that I delete has the following values (which are headings): Col 1, 2, 3. The populated columns are A - L
Channel
Alrt Type
Alrt Dt
I have a pivot table that looks like this:Red Yellow OrangeApple .................12
Banana ......................10
Strawberry ..........8
Carrot ..................................2
I want to know the colour of each fruit, for example:
Apple Red
Banana Yellow
Strawberry Red
Carrot Orange
What formula should I have in the second column to find the colour of the fruits?
I'm really tired of searching manually for the column containing a heading, as I have many columns. Isn't there a "simple" way to make a dropdown containing all the column headings in row 1, and upon selecting the heading of interest, have excel "go to" that column (maybe using a Find macro)?
I have a 10x10 table. First row contains column titles (plain text, hardcoded). Each line below them has a 1 in *just one* cell. So, each line contains 9 zeros and one 1.
In the 11th column I want to check which column has the 1 in it and return the column's title. So, if the 5th row has a 1 in cell E5 I should get, in K5, the value of E1 (the title of the E column that is).
method to copy some data to a variable place in the worksheet.
Column A has team names with data in columns B,C,D and E.
I would like to group together the data for each teamname - so have the team names in Cells H5,H10,H15 and H20.
I have attached some sample data
I'm trying to enter info in a cell based on what is entered in two other cells.
So I have a dropdown box for the species on another sheet as well as a dropdown box for rail sizes. Then I have to crossreference them to come up with an upcharge number. Sorry for my basic lingo. I hope it's understandable.
is there any way of shortening this code below , i am just formatting the heading of a sheet
Range("A1:I1").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Calibri"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone.............
I have a row of Headers A1 thru AO18. What I'm trying to do is have a small macro to go out and find "template" whichever column it may be in and insert a column to the left. I can make Excel insert the column to the left but obviously I can't have a fixed column designation because it will keep moving to the right of it and it won't work anylonger or I should say it doesn't put the column where I want it.
View 7 Replies View RelatedMy problem is that the sequence of Colunms in my worksheet, with the similar data, keep changing. For example, an "Employee Name" may appear in column 3 or 5 etc.
For this reason, I want to select a cell within any column by its heading rather than "A", "B", "C" etc. For example, I want to select a cell as below:
Cells(5, "SN").Select
- which of course is not accepted by Excel
Similarly, I want to use Range with a column by its heading.
Or
I will like to know the Column number for the column with the heading "SN", for example.
I have the following example:
Math Science Biology
A Dave Pete John
B Mary Cate Dave
C Jack Dave Bob
How can I have excel give me all the subjects as well as classes that Dave teaches ie. Math(A), Biology(C) and Science(B)
Sub addInterestingDataPoints(myChart As chart)
'code goes here
End Sub
The above code is causing a Compile Error: variable not defined (highlights the sub heading line).
Let's say I have data that looks like this grid below. Each color represents a column heading and each number is a data point within that column.
Blue Red Green Yellow
18 27 15 36
56 41 3 22
Can I write a formula that would do the following 2 things with the data in this format:
1. Find the max of the data...simple =Max(...) formula
2. Use the max to return the column heading. In this case the max is 56 so the value I want to return is "Blue"
I have a Meeting Place and Cities Table(too large).An example attached as a file and explained waht is wanted.
View 2 Replies View RelatedI want to lookup the max value within a group of columns and return the column heading (specified a cell) that corresponds to the max value.
View 9 Replies View RelatedI am am wanting to delete the entire column if there are no entires below the heading in Row 1.
So in image below, Col C, E & G are to be delete including the heading.
I use this code to hide the tool bars in a workbook, the code works but when I go to a new sheet the row and column headings are visible. I tried several things that didn't work.
Code:
Private Sub Workbook_Open()
Application.ScreenUpdating = False
On Error Resume Next
With Application
.DisplayFullScreen = True
[Code] ..........
I know this is probably very simple and basic but I have a workbook with a total of 8 worksheets.
The first sheet is going to be a summary of information contained on the 7 other worksheets.
All 7 sheets have the same header row and I would like to have it so that all 7 rows repeat the same headings and if I add more sheets they automatically also have the heading row appear.
Is this possible other than doing a copy paste when you insert a new sheet? Also is it possible to code it so that should a new colum (heading) be added to the first worksheet it would automatically be added to all the other sheets?
The first summary sheet will have headings that reflect the name of the employee and then the names of the following worksheets as the header row and will gather information about the progress of each employee on fulfilling the requirements setup on the other worksheets.... that is yet another question on how to get it to do this.... which I will post later..
I've got a table with columns for each day of the month, the second row contains the days of the week (MON, TUE, WED, etc...). I want to have two cells at the end of each line, one for summing the numbers in the working days (mon-fri) and another - for the sum of the numbers in the weekend cells.
The idea is the change the content of the second row every month so that the days of the week correspond with the respective date.
what formulas should I employ to accomplish this task and how exactly to do that?
I have a workbook with many sheets that at times is handy to have the gridlines and headings on and off at other times.
Is there a way to change in VBA the True/False setting for gridlines and row/column headings displayed on the worksheet, for all visible worksheets without making each sheet active to toggle the settings.
With ActiveWindow
.DisplayGridline=False
.DisplayHeadings=False
End with
Is there another way to do the same thing by looping through the sheets:
For each sheet in this Workbook.Worksheets
????? 'set gridlines to false
????? 'set headings to false
Next sheet
user enters 3 headings and its data using user Form one by one. There are three buttons EnterData, EnterColumn and Cancel. In textbox2, user enters heading of a column heading. in Textbox3, user enters data for the heading. First column's heading is asked then its data. Once user presses Cancel, second column's heading is asked and its data is asked until User presses Cancel and so on The code does not work? It places values at wrong places.
'Asking for heading, Textbox2 is placed next to EnterHeading button:
Private Sub EnterHeading_Click()
For j = 1 To 3
i = 1
Data1 = TextBox2.Text
Do
Call data
Loop Until Call Exit Sub --- How Do I say until Cancel Is Pressed?
End Sub
Sub data() - Data procedure, which adds data into row:
i = 1
i = i + 1............................