Return Column Letter Based On The Letter In A Cell.

Dec 31, 2009

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View 9 Replies


ADVERTISEMENT

Return The Letter Value Of A Column

Aug 28, 2008

When I used "activecell.column" it returns the numerical value of a column (For Example: Column B would be 2).

I use the following in my
Dim StrRef As String
StrRef = "AN"

I want to be able to refer to column "AN" without having to hardcode "AN" into my code becuase it may change.

Below is part of the Code where I refer to "AN" in context of a loop---

For Each icell In ActiveSheet.Range(StrRef & "7" & ":" & StrRef & LastRow)

I tried Activecell.Address but that return the entire cell reference.

View 9 Replies View Related

Return Last Used Column Letter

Apr 22, 2008

I need to find the last used column in the excel sheet. I used the below mentioned code to find the last used column but it gives the ouptut in integer.. for eg) output is 13 if the column is M, but I need to get M as output.. how do i do that

Sub FindLastColumn()
Dim LastColumn As Integer
If WorksheetFunction. CountA( Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox LastColumn
End If
End Sub

View 3 Replies View Related

Column() To Return A Letter Instead Of A Number

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

Return Column Number Not Letter

Sep 4, 2006

How to return column number (not letter)?

View 5 Replies View Related

Return Number Based On Letter Position In Alphabet

Aug 27, 2007

i wish to number the cells bases on the cell on the right.

Heading
A
A
A
A
A
A Total
B
B
B
B
B Total
C
C
C Total
D
D
D
D Total

to

Heading
1A
1A
1A
1A
1A
1A Total
2B
2B
2B
2B
2B Total
3C
3C
3C Total
4D
4D
4D
4D Total

The values in place of A,B...can range between 1 to 6.

View 9 Replies View Related

Search Row For Text And Return Column Letter Of First Occurrence?

Dec 14, 2011

Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?

View 8 Replies View Related

Does A Property Exist To Return The Letter Reference For A Column

Jan 24, 2008

I need to build a formular in a cell that uses various columns in it's row such as: =A2+A4+A17+A24. In the past I've used simple number references to the columns (a is 1, b is 2,.....) I can't do tht though if I'm using a macro to automate the creation of a formula, right?

Is there a property I'm missing that will return the letter reference to the column instead of just the number as Activecell.column does?

My alternative is to write my own code to do this, but you know the bit about not reinventing the wheel....

View 9 Replies View Related

Find/Return Nth Letter In Cell

Jun 12, 2008

I'm trying to write a macro where I match specific letters from one cell, with a column in another sheet so I can copy/paste adjacent cells. As an example I have a list of part numbers in column A of my Document1:

ex.
C12932-1
C13226-2

I want to be able to find the corresponding part number in column A in Document2:
ex.
C-12932
C-13226

However the proplem is clearly that the part numbers are written differently. So I need to just search for "12932" or "13226"

View 3 Replies View Related

Convert Lower Letter To Capital Letter Automatically

Mar 2, 2009

When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?

View 9 Replies View Related

Return Data Infront Of Specific Letter In Cell

Jul 14, 2008

ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.

A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'

I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?

View 10 Replies View Related

Returning The Column Letter Based On A Search

Jan 28, 2008

I'd basically like a formula that finds a number (eg 220) within either the first 10 rows (or with row 5 if this isn't possible) and returns the column Letter as the result. The number will only appear once and only in one column. If it matters the columns go all the way to EC.

View 9 Replies View Related

Change Letter Case Of Cells First Letter Only?

Mar 7, 2014

I need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.

So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter

View 3 Replies View Related

Insert Column Macro Based Variable String Letter

Jun 11, 2008

I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

View 4 Replies View Related

Remove The First Letter In Each Cell In A Column

Mar 5, 2009

I have a list with a column where all cells begins with the letter D. I would like to remove that D from all cells. For instance;

Dxxx1
Dxxx2
Dxxx3
Dxxx4

Should be;

xxx1
xxx2
xxx3
xxx4

View 9 Replies View Related

Cell Column Letter In Formula

Oct 29, 2006

I want to put the letter A in cell a1(or any cell in column A, SHEET1) of SHEET 1 and get the information from SHEET 2, column A. Put this information in column B sheet 1. I will have about 40 columns in sheet 2. I used a if formula, but only 8 ifs can be used.

View 9 Replies View Related

Auto Complete Cell Based On 1st Letter

Jul 1, 2008

I would like to have excel automatically fill an excel cell with a predefined list. For example, if you put the letter l it will automatically include the word large. This is like you do when you fill out a form online. See attachment

View 2 Replies View Related

Trying To Create A List From A Database Based On A Letter Placed In 1 Cell

Jul 15, 2009

Its really hard to explain. See the attached.. Basically I have kind of a database of Letters and each letter has several codes associated with it, although each letter is different (a few are the same but that's irrelevant), e.g. letter E means you need to use codes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.9, and 3.10. That information is stored elsewhere. I simply want a user to be able to put that letter into this box and next to it these codes will populate.

View 4 Replies View Related

Fill Down But Have Column Letter In Formula Change And Not Cell Number

Dec 30, 2009

i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.

View 9 Replies View Related

VBA Change Cell Value To Specific Letter Based On Value Of Different Cell In Different Worksheet

Jun 6, 2014

I've done the following:

Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub

However, this doesn't even work.

When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...

View 5 Replies View Related

Return The Largest Letter In An Array

May 15, 2007

how I can return the largest letter from an array of letters? e.g. if the array is "A,B,C,D,H,K,L,O,P", the value returned would be P. Kind of like a MAX function, but for letters instead of numbers.

View 9 Replies View Related

Data Validation Format Letter Number Letter Number Etc.

Jul 11, 2009

I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

View 14 Replies View Related

Subtracting From Column Letter

Oct 22, 2007

Is it possible to subtract a number from a column letter? I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter. Example F-5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.

View 5 Replies View Related

How To Check For A Particular Letter In A Column

Jun 5, 2009

Basically, I need to be able to determine whether a particular letter is stored with another letter in the 'CG column' of the attached spreadheet.

The criteria is this:

1. Letter J can be present as long as there is no other letter in that column.
2. Letter B and D cannot be together in the column, but B can be with any other letter and likewise for D.

Basically, I could have a load of Js in the CG column and that would be fine. If another letter was with those Js, I need a warning box. I could have a B,H,S,T etc.....and that would be fine, but as soon as a D is entered, I need a warning. Similarly, if I had D,H,I,U,T etc......that would be fine too, but as soon as a B is entered, I need a warning again.

View 13 Replies View Related

Way To Get The Column Letter, Instead Of Number?

Nov 13, 2006

Is there a way to get the column Letter, instead of Number?

like in A1 Column() would equal 1 in B1 Column() would equal 2

I would like

in A1 Column() to equal A and in B1 Column() to equal B

View 9 Replies View Related

Column Letter Instead Of Number

Jul 26, 2006

is there a way to make the following code return the letter of the column instead of the number? currently if the 'String' value that is in 'ColumnFind' is in column B this code returns a value of 2. i Need the 'B' for later code to work.

ColumnLetter = ColumnFind.Column

View 5 Replies View Related

Assign Folder Based On First Letter Of Name

Jan 2, 2014

I have a routine that looks a variable's first letter and then assigns it to a specific folder. I have tried using just the first letter (firstletter) and the ASCII value of the first letter but neither seem to work - see below.

firstletter = Left(rng.Value, 1)
iletter = Asc(firstletter)
MsgBox iletter
If iletter < 71 Then cmfolder = "CM A-F" Else
If iletter > 70 And iletter < 77 Then cmfolder = "CM G - L" Else
If iletter > 76 And iletter < 83 Then cmfolder = "CM M - R" Else cmfolder = "CM S - Z"
MsgBox cmfolder

View 1 Replies View Related

Sum Based On Left 3 Letter Criteria

Aug 10, 2006

I'm looking for is a way to sum a bunch of numbers using a sum if statement that start with PRJ and are followed by any combination of five numbers. Like this

=sumif( Range, PRJ#####, SUm range)

View 6 Replies View Related

Convert Column Letter To Number

Nov 22, 2005

I need to convert a column letter to a column number for the following line of code in my VBA application:

VB:
strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"

I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in the middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?

View 9 Replies View Related

Letter Color And Column Sorting

Dec 20, 2008

I have Danish Office 2007.

1) Let's say I have 3 columns (horisontal) and many rows (vertical. Each row goes together, as the first column can be text that describes a transaction. The second column will be the amount of money and the third can be something else.
I know how to sort these by whichever column I want to. But the problem is every cell needs to be the same size. And I have merged 3 cells in the first column and only merged 2 cells in the last two colums.
So Excel tells me I cannot merge unless each cell is the same size.

Is there a solution here? I need the 3 cells in the first column, so I have enough room to describe the transaction. And to avoid wasting space, I need to only have the other two columns be the size of two merged cells each.

Example, though with the text in another column:
Picture of it

2) The other problem is that I would like a cell to display the number in red if below zero, green if above zero. I cannot do this. I know where to put in the format codes, but I don't know what to write.
At the same time, I need the cell to show the currency in Danish Kroner. So I need a format code that does this. Someone told me this:
[Blue]dk #.##0;[Red]dk #.##0;[Green]*dk #.##0

But that doesn't work, nor if the color is in danish. What do I do?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved