Adding ROW Using 1 Column As Reference And Another Column For Naming
Nov 19, 2013I Basically need to use DATA in Column D of my file to add a ROW and then use Column C to name that new ROW added...
Example: [URL] ........
I Basically need to use DATA in Column D of my file to add a ROW and then use Column C to name that new ROW added...
Example: [URL] ........
I have a table. I have added a new column on the far right, and as I don't know how many columns my table has I simply use:
[Code]....
Now I want to assign a name to that column; but as I don't know the column position, or number, how do I do that???
Now I need to place some information in the cells in that column. If I know the position of the cell we just placed the header name in, I know I can use offset to accomplish this, but:
How would I know the location of that cell other than running a "Find" on the entire header row.
Is this the easiest/most efficient way to do so?
I have a similar question as displayed in this post, but I only need to calculate the last entered value in a column.
I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.
The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.
This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).
For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units.
In points, the difference is 420 vs. 386.25, or 33.75 points.
Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.
Then (10-1) margins allowances time 3.75 points resolves the difference.
Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design.
For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10).
Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.
I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.
I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Example.xlsx
I have a huge db that has a column "C" with a list of names. I have been creating tabs and naming them according to that list. I wonder if there is anyway I can create and name the tabs automatically. I already searched for threads in the forum, but only found this this backwards "tabs to cells". By the way, the names in column "C" sometimes repeat.
View 2 Replies View RelatedIf i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.
Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...
i'm not even sure if this makes sense lol... let me use example
on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6
in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.
I have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
If column B is not empty, I want the corresponding cell in the same row of column A to contain the value "1".
View 9 Replies View RelatedCurrently, I have a standard vlookup forumla that looks like =Vlookup(B2,Sheet2!A:G,5,false)
However, the data on sheet two is ever expanding--each month, specifically. And I need to return the most recent (so, rightmost filled) column. Right now, the column is 5, but next month it will be 6 and so on and so forth.
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
I am having a column which has numbers. The length of a number should be 8 or should be 8 digit.I want to standardize the columns by adding leading zero. For example
Example Output
1245 00001245
12 00000012
5 00000005
1234567 01234567
I need a formulas to add the row & column amount , see the attached example sheet.
Row and Colums.xlsx
I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?
View 14 Replies View RelatedThis is a simple one, for someone who is smarter than I. I need to add a column but omit the rows that are blank or has a zero value in either row C or D. In other words I want to add every row in column C if there is a value in row C&D of that item. Attached is a small example.
View 2 Replies View RelatedI've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?
View 5 Replies View RelatedI have the following code which deletes the specified value from each cell in column B that contains that value, (note it only deletes the value from that column and not all in the row).
Code:
Sub delete1()
Dim lr As Long, i As Long
With Sheets("Database")
lr = .Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If .Range("B" & i).Value = 1 Then .Range("B" & i).ClearContents
Next i
End With
End Sub
Now what I need is to adapt it slightly so that it also adds a comment to the cell in Column I and the current date in Column L, (on each row where the deleted value was).
The comment would be something like "old data archived" and the date in any format, preferably dd/mm/yyyy.
I've been searching for the answer but either I do not understand or it isn't out there. I want to add every other column in one row resulting in an average of all the columns in that one particular row.
View 8 Replies View RelatedHow do i go about adding cells when some have the #n/a error?
View 9 Replies View RelatedCan I have a macro code to insert a row from column A:K.
I have data in column L to IV which I want to stay as it is. i.e. for example when a row is inserted from A:K, that row should not extend beyond Column K.
How do I add a column of cells with formulas without getting #DIV/O! for the total?
View 9 Replies View RelatedI have several columns of times in the HH:MM.SS format. I need a sum at the bottom of each column.
If I put a Sum or a Cell+Cell formula in, it gives me the #value error. I've tried a few different things but can't get it to work.
Could someone be so kind as to tell me the formula or point me to the instructions for this?
(The data reflects times spent on certain tasks for each day, and I need a sum at the end of each column giving me a total amount of time spent on that task for the month.)
Please refer to attached sheet.
I have monthly data for 3 stores as shown.
I want to know if any data exsit at certain cell location.
See data in Cell X1:Y4
For Store1:
I want to check if any data in cell reference (10,12), where 10 and 12 is variable and values will be in cell Y2 and Z2 respectively and it will change month to month.
If I have data in this cell reference, then AA2=TRUE, if no data then AA2=FALSE
For Store2:
I want to check if any data in cell reference (110,12), where 110 and 12 is variable and values will be in cell Y3 and Z3 respectively and it will change month to month.
If i have data in this cell reference, then AA3=TRUE, if no data then AA3=FALSE
For Store3:
I want to check if any data in cell reference (210,12), where 110 and 12 is variable and values will be in cell Y3 and Z3 respectively and it will change month to month.
If i have data in this cell referance, then AA4=TRUE, if no data then AA4=FALSE
I'd like to add the numbers in column C that correspond with Fuji, Fuji Royal, or Fuji Premium in Column B. Essentially I am trying to count up the total number of all variety of Fuji apples. I've tried using the sumif and sumifs formula, but haven't been able to crack it.
RED APPLESSIN ESPECIFICAR2.352
RED APPLESROYAL GALA1.029
RED APPLESCRIPPS PINK280
RED APPLESROYAL GALA896
RED APPLESCRIPPS PINK1.064
RED APPLESFUJI867
RED APPLESFUJI PREMIUM1.919
RED APPLESROYAL GALA4.871
RED APPLESCRIPPS PINK1.176
RED APPLESFUJI112
RED APPLESROYAL GALA1.064
RED APPLESSIN ESPECIFICAR2.282
RED APPLESSIN ESPECIFICAR4.522
RED APPLESCRIPPS PINK1.176
RED APPLESROYAL GALA2.352
RED APPLESCRIPPS PINK7.056
RED APPLESFUJI1.96
RED APPLESPINK LADY1.344
I have 5 coloums in which the last three have numeric values. The first two coloums have name and country code. Now, The first two coloums have duplicate values but the rest of the corresponding columns have different numeriv values. What I want now is to add the numeric values of the coloumns of the duplicate data and want to display in the first row. For ex there are A = name , B= Country , C D and E contains different numeric values. A and B have dupilcate data but different C, D and E values. Now I want to add up the numeric values of the dupilcate data and get it displayed in a new coloum in the first row of the duplicate data set.
View 1 Replies View RelatedI have about 5000 rows of data and I need to add the same letters (.jpg) the the end of all the data in a column.
View 4 Replies View RelatedI've got a sheet with 3 columns A, B and C headed: "Surname", "Forename", "Membership No" with then a variable number of rows containing the data. There are no blank cells (the macro already checks for this).
In column D I have "Group Name" which I need to populate with GrpA for the first 250 members, GrpB for the next 250 and so on down until it stops with the first blank cell in column C. At the moment I'm happy for it to cope with up to 2000 members, i.e. 8 groups, though may need to extend it later (no danger of getting past GrpX though!).
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.
Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell.