Return Column Number Of Heading
Jun 9, 2008
I have range like (3E:3K) in this range are only 2 different expressions. "Result and " forecast". I wanna look from left to right for the first cell with the string "forecast".
And than I want to work with the column of this cell, only this column.
How do i do that?
Edited
I corrected the coordinates. I want to look in a specific column line (Nr.3) from there the first Cell with "Forecast" and from that String the row number.
They following 4 posts were based on wrong informations by me.
View 8 Replies
ADVERTISEMENT
Apr 14, 2010
I 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 Related
Sep 16, 2007
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View 4 Replies
View Related
Feb 21, 2014
Encryption algorythim.xlsx
Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.
As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)
What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)
This shouldn't be too hard seeing that the data in the columns has no repeats.
View 6 Replies
View Related
Nov 18, 2008
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.
Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.
View 5 Replies
View Related
Nov 23, 2007
My 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.
View 2 Replies
View Related
Jul 11, 2014
I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.
I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.
Multiple commodities and multiple engineers should be separated by definite structure.
find worksheet with input and output (manual) as expected.
View 2 Replies
View Related
Mar 12, 2007
This is a double IF() to me. I just can't figure it out.
I download a list of classes taught by a number of teachers. I want to summarize how many of classes taught by each teacher.
I have tried many variations of countif() but can't seem to figure out how to do both.
Example copied in below:
ClassesTeacher Classes Taught Angela Barbara Bob
mathAngela math 2 0 1
englishBarbara english 0 2 0
musicBob music 1 0 1
mathBob
englishBarbara
musicAngela
mathAngela
englishAngela
musicBarbara
mathBob
englishBarbara
musicAngela
mathAngela
englishBarbara
View 9 Replies
View Related
Aug 9, 2013
I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below
Red
Green
Blue
Orange
Yellow
Result
2
4
3
6
1
[code]...
I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers
View 4 Replies
View Related
Mar 23, 2014
I 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.
View 6 Replies
View Related
Jan 12, 2010
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).
View 9 Replies
View Related
Sep 2, 2007
I have a spreasheet with partnumbers listed in column A, a safety stock value in colum B, a restock date in column c, and quantities listed by month needed in the following columns, i.e.:
A B C D E F G
Part # Safety Stock Restock Jan Feb Mar Apr
123abc 50 Feb 20 35 20 10
In the example above, it is apparent that the safety stock will be delpeted in Feb.
I am trying figure out how to calculate the month that the safety stock will be depleted
and show this in another cell.
View 6 Replies
View Related
Oct 11, 2007
How do you count the number of columns in a range in order to reach ( find) a specific value in that row ?
View 5 Replies
View Related
Mar 8, 2008
I have inventory in 9 cities. Cities names are in B2-J2. Line items are rows 3-100
In column K, I have the max formula for that row. What I need is, in column L, the name of the location city from row 2 where the max qty is located also color the max qty in the row.
View 2 Replies
View Related
Nov 18, 2013
I have a number of dates (columns) and under each date there is the demand value (Rows). Also, i have a column that has the current inventory. what im trying to do is to keep on adding the demand in one row (i.e multiple dates) until the sum just exceeds the inventory. After that, i would like to return sum the date at which we stopped adding. the point of this excercise is to see at which month will our inventory deplete according to the demand. Below is an example solved by hand.
Demand
Part #
month 1
month 2
[Code].....
View 9 Replies
View Related
Sep 8, 2007
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
View 5 Replies
View Related
Mar 1, 2008
Attached is a very small sample of a workbook that looks at community impact; before, during and after a project.
The source data (not shown) is down loaded from a system that can only supply the date in reverse order (see column A).
i.e. 20080229 = 29-Feb-2008. I've applied a conversion in column "C"....
= DATE(LEFT($A2,4),MID($A2,5,2),RIGHT($A2,2))
The 'properly' formatted date is then used to determine when on the scale of observation that given date falls.
e.g. the number of weeks before, during and after the project.
This is determined by the 18 week date 'matrix/ array' in columns F to X the red box is used to input the project start date, (& assumes the project runs to its normal 6 week time scale, it get REALLY messy if the boss prolongs or cuts short the project!)
From the result various figures and a graph using the properly formatted date are calculated, produced and presented showing....
The scale of the problem {before}
The impact of the project {during}
The lasting efftectivness {after}
You can see that I've managed a 'workaround' for the limit on the number of IF statements allowed in one formula using a rolling formula in columns; B then Z - AQ. This works OK, but!! combine that with the other formulae doing the impact calculations and 20000+ rows of data (with the acssociated workings in B + Z-AQ) the effect is to make make the file over 15Mb for 1!! worksheet, (the limit for emailing on the jobs system is 3Mb!!) & just to really make it scary sometime there are 2 projects on the go at once.
Before I created the attached the user responsible for these graphs and calculations manually counted dates & typed/copied & pasted the week number/tile, initially several days work followed by half a day every weekly update!
View 4 Replies
View Related
Feb 7, 2014
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
View 3 Replies
View Related
Feb 19, 2009
I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.
In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.
View 2 Replies
View Related
Apr 4, 2014
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
View 4 Replies
View Related
Jan 29, 2014
The yellow highlighted column F (each Reason) in the sheet2 to be placed as main header in the next column and so on as seen in the sheet 3
If they have two similar item number with 2 different reasons - delete the duplicate item number and place the number in each column according to its reason as seen on row 16 on the sheet3
If they have two number with similar reason - just sum up the number
sample excel 2.xlsx
View 1 Replies
View Related
Mar 2, 2011
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
View 7 Replies
View Related
Feb 10, 2010
Is there a formula to isolate observations in the same column (different values) and also all have the same column heading like the file attached?
View 2 Replies
View Related
Oct 28, 2011
How to get the Column Alphabet based on the Data it contains or the Column Heading using VBA?
Is it possible to get the Column Alphabet using any Macro or any function that within a Module it can always take the New Column Name during Execution..
Example: I have certain Columns where I have Yes and No Tick using the Wingdings P and Y..
Now these Columns are alternately Placed and there are six columns in all and they are spread over 12 Columns as the Alternate COlumns is reserved for Manual Entries.
Now I intend to increase the Manual Entry Columns but the problem is that every time I do that I need to make changes in my VBA Code.
Is it possible that even when the Columns are Inserted or Deleted in between before or after these columns I dont need to change the Explicit references by changing some approach.
I am not fuly conversant in VBA but use it whenever things are not completely feasible with Formulas AFTER GOOGLING.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("X3:X13")) Is Nothing Then ' You can Change the range here
Cancel = True
[Code]....
As you can see the Ranges are Alternately Placed and there are about 6 such Columns with the above TICKS..
Now, how do I keep it FLoating so that even after changing the COlumn Location it does not need to get Updated i.e. X and Z Column Alphabets..
View 9 Replies
View Related
Sep 3, 2008
how to find text from a row in column, where is a lot of text and return row number, of located text?
View 15 Replies
View Related
Jun 1, 2008
I know the column number (57) and want the formula to return the column name (BE) Auto Merged Post Until 24 Hrs Passes;not using vba that is! or if so , a custom function so macros dont have to be run.
View 3 Replies
View Related
Feb 14, 2006
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?
=INDIRECT(row() & column())?
View 14 Replies
View Related
Sep 4, 2006
How to return column number (not letter)?
View 5 Replies
View Related
Aug 21, 2007
I have a column with zeros, positive numbers, and negative numbers. I need to find the last (bottom) positive number in the column.
Ex:
Column A has -
$50
$0
-$10
$22
$0
-$14
I want a formula or function in B1 that would return $22. However if I add a new row under the -$14 which contains $37, the formula should then return $37.
I have been googling all night, and I can't make this work.
View 8 Replies
View Related
Dec 18, 2007
I have a Range variable that points to a particular cell. If I wanted the address, I would do, cellName.Address. This returns, for example, "$F$3". If I wanted to have the column "F" as a number (column F would be the 6th column, so 6), how could I do that without storing some kind of array of letters and number equivilants. It would also have to work past the 26th column when the column addresses switch to, like, "AA", "AB" etc.
View 3 Replies
View Related
