How to replace column letter in refferences with a function using the old column letter?

Hello
How to replace column letter(s) (or column numbers) in refferences with a
result of a function using the old column letter(s) (or column numbers)?


0
kopn (164)
10/12/2005 12:19:17 PM
excel 39879 articles. 2 followers. Follow

6 Replies
415 Views

Similar Articles

[PageSpeed] 48

I think you mean

=INDIRECT(A1&7)

where A1 holds the letter in this instance.

-- 
 HTH

Bob Phillips

"Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl...
> Hello
> How to replace column letter(s) (or column numbers) in refferences with a
> result of a function using the old column letter(s) (or column numbers)?
>
>


0
bob.phillips1 (6510)
10/12/2005 12:59:49 PM
Thanks for your reply.
How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
automatically?
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
�������� ���������: news:uWkLnzyzFHA.1640@TK2MSFTNGP12.phx.gbl...
> I think you mean
>
> =INDIRECT(A1&7)
>
> where A1 holds the letter in this instance.
>
> -- 
>  HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl...
> > Hello
> > How to replace column letter(s) (or column numbers) in refferences with
a
> > result of a function using the old column letter(s) (or column numbers)?
> >
> >
>
>


0
kopn (164)
10/12/2005 1:19:21 PM
=INDIRECT(A1&ROW())

-- 
 HTH

Bob Phillips

"Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
news:%234JbQ%23yzFHA.904@tk2msftngp13.phx.gbl...
> Thanks for your reply.
> How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
> automatically?
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
> �������� ���������: news:uWkLnzyzFHA.1640@TK2MSFTNGP12.phx.gbl...
> > I think you mean
> >
> > =INDIRECT(A1&7)
> >
> > where A1 holds the letter in this instance.
> >
> > -- 
> >  HTH
> >
> > Bob Phillips
> >
> > "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> > news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl...
> > > Hello
> > > How to replace column letter(s) (or column numbers) in refferences
with
> a
> > > result of a function using the old column letter(s) (or column
numbers)?
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
10/12/2005 3:21:48 PM
Thanks!
How to convert a column number to a corresponding column label, letter?
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
�������� ���������: news:eN7s8C0zFHA.720@TK2MSFTNGP15.phx.gbl...
> =INDIRECT(A1&ROW())
>
> -- 
>  HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> news:%234JbQ%23yzFHA.904@tk2msftngp13.phx.gbl...
> > Thanks for your reply.
> > How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
> > automatically?
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
> > �������� ���������: news:uWkLnzyzFHA.1640@TK2MSFTNGP12.phx.gbl...
> > > I think you mean
> > >
> > > =INDIRECT(A1&7)
> > >
> > > where A1 holds the letter in this instance.
> > >
> > > -- 
> > >  HTH
> > >
> > > Bob Phillips
> > >
> > > "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> > > news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl...
> > > > Hello
> > > > How to replace column letter(s) (or column numbers) in refferences
> with
> > a
> > > > result of a function using the old column letter(s) (or column
> numbers)?
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
kopn (164)
10/13/2005 6:11:50 AM
I use a simple UDF

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
    sColumn = Split(Columns(Col).Address(, False), ":")(1)
    ColumnLetter = sColumn
End Function

-- 
 HTH

Bob Phillips

"Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
news:OnqmQ07zFHA.464@TK2MSFTNGP15.phx.gbl...
> Thanks!
> How to convert a column number to a corresponding column label, letter?
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
> �������� ���������: news:eN7s8C0zFHA.720@TK2MSFTNGP15.phx.gbl...
> > =INDIRECT(A1&ROW())
> >
> > -- 
> >  HTH
> >
> > Bob Phillips
> >
> > "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> > news:%234JbQ%23yzFHA.904@tk2msftngp13.phx.gbl...
> > > Thanks for your reply.
> > > How to change =C1 (any reference) to =INDIRECT(A1&1(the same row
number)
> > > automatically?
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
> > > �������� ���������: news:uWkLnzyzFHA.1640@TK2MSFTNGP12.phx.gbl...
> > > > I think you mean
> > > >
> > > > =INDIRECT(A1&7)
> > > >
> > > > where A1 holds the letter in this instance.
> > > >
> > > > -- 
> > > >  HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> > > > news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl...
> > > > > Hello
> > > > > How to replace column letter(s) (or column numbers) in refferences
> > with
> > > a
> > > > > result of a function using the old column letter(s) (or column
> > numbers)?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
10/13/2005 7:32:48 AM
Thanks.
I have to make a 2004 year calculation using 2004 year table in the same way
a 2003 year calculation which used 2003 year table was made. I copied
formulas from 2003 version table to 2004 version table. The 2004 version
table has different columns order therefore column letters in the formulas
are incorrect. I want to change the column letters to correct ones according
to 2003 and 2004 column lables located in a top row in the tables. I have to
use columns with the same lables as in 2003 year in formulas. How to change
the column letters to correct ones in all cells automatically? I do not need
to change row numbers because they are correct.
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
�������� ���������: news:e9anih8zFHA.612@TK2MSFTNGP10.phx.gbl...
> I use a simple UDF
>
> '-----------------------------------------------------------------
> Function ColumnLetter(Col As Long)
> '-----------------------------------------------------------------
> Dim sColumn As String
>     sColumn = Split(Columns(Col).Address(, False), ":")(1)
>     ColumnLetter = sColumn
> End Function
>
> -- 
>  HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> news:OnqmQ07zFHA.464@TK2MSFTNGP15.phx.gbl...
> > Thanks!
> > How to convert a column number to a corresponding column label, letter?
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/�������� �
> > �������� ���������: news:eN7s8C0zFHA.720@TK2MSFTNGP15.phx.gbl...
> > > =INDIRECT(A1&ROW())
> > >
> > > -- 
> > >  HTH
> > >
> > > Bob Phillips
> > >
> > > "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> > > news:%234JbQ%23yzFHA.904@tk2msftngp13.phx.gbl...
> > > > Thanks for your reply.
> > > > How to change =C1 (any reference) to =INDIRECT(A1&1(the same row
> number)
> > > > automatically?
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> �������/��������
�
> > > > �������� ���������: news:uWkLnzyzFHA.1640@TK2MSFTNGP12.phx.gbl...
> > > > > I think you mean
> > > > >
> > > > > =INDIRECT(A1&7)
> > > > >
> > > > > where A1 holds the letter in this instance.
> > > > >
> > > > > -- 
> > > > >  HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message
> > > > > news:%23bHpscyzFHA.1968@TK2MSFTNGP10.phx.gbl...
> > > > > > Hello
> > > > > > How to replace column letter(s) (or column numbers) in
refferences
> > > with
> > > > a
> > > > > > result of a function using the old column letter(s) (or column
> > > numbers)?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
kopn (164)
10/13/2005 8:09:33 AM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

total group & max function
I am using Access 2003 I created a Db for Machine Mtce. The table consists of 3 tables, (1) Basic Mach Infor (ie Mach#, Mach Name, Mfg etc) - (2) Operations Table - consists of different prev mtce (PM) that need to be completed at various time frames. Ex: "Mach 100" could have 3 different operations; Oper # 100A to change the oil due every 90 days, Oper # 100B to check the belts due every 180 days, Oper # 100C to grease the machine due every 365 days. The 3rd table is the "PM History" table which includes the history of all of the PM's completed with Work Order...

Function to determine Directory Size
I'd like to be able to determine the size of a directory or group of files. Does anyone know of an add-in that has a function whereby I can specify a path and get the size of directory of groups of files as the return value? Ex: =dirsize("C:\Documents and Settings\Bob\My Documents\School") =dirsize("C:\Documents and Settings\Bob\My Documents\School\*.doc") It would also be helpful if there were an option to include subdirectories. -- Thanks, Bob Chmara Argh.... Make that "get the size of a directory or group of files" Bob "Bob Chmara" fatfing...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

Excel Text Function (Right, Left)
My sheet has columns like this A 1. 63mmx 4kg 2. 75mmx 4kg SWR How can i detect that if a1:a2 is right,3 "swr", that it should be returned 3 otherwise 6. How can i add =right function with conditional formula. Help me Rao Ratan singh Hi =IF(RIGHT(A1,"SWR"),3,6) fill down your column (move cursor over bottom right hand corner of the cell when you see a +, double click) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:D...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Weekday function
Does VBA have a function which accepts an integer input between 1 and 7, which returns a string value to represent the corresponding day of the week?? For example, if you give the function an integer value of 1, then it should give back a string of "Sunday"... and the number 7 should return "Saturday". I know I can easily write my own function, but I'm curious if VBA provides a function already. No need for me to reinvent the wheel. thank you Don't think so, you would need to write one Function MyWeekDay(DayNum As Long) As String Dim Days A...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...