Column names

Is there any code to convert column(number) to column(letters), and V.V.
e.g.  27 for AA,     AB for 28
Thanks 


0
danchen (87)
4/30/2005 8:25:46 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
586 Views

Similar Articles

[PageSpeed] 56

=COLUMN(AA:AA)

-- 
Regards,

Peo Sjoblom


"daniel chen" <danchen@worldnet.att.net> wrote in message 
news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
> Is there any code to convert column(number) to column(letters), and V.V.
> e.g.  27 for AA,     AB for 28
> Thanks
> 

0
terre081 (3244)
4/30/2005 8:38:58 PM
Hi, Peo
Great! Thanks. How about the other way?
i.e.  27 for AA

"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:%23MpiXScTFHA.2560@TK2MSFTNGP09.phx.gbl...
> =COLUMN(AA:AA)
>
> -- 
> Regards,
>
> Peo Sjoblom
>
>
> "daniel chen" <danchen@worldnet.att.net> wrote in message 
> news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
>> Is there any code to convert column(number) to column(letters), and V.V.
>> e.g.  27 for AA,     AB for 28
>> Thanks
>>
> 


0
danchen (87)
4/30/2005 9:31:25 PM
How about:

=SUBSTITUTE(ADDRESS(1,27,4),"1","")



daniel chen wrote:
> 
> Hi, Peo
> Great! Thanks. How about the other way?
> i.e.  27 for AA
> 
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:%23MpiXScTFHA.2560@TK2MSFTNGP09.phx.gbl...
> > =COLUMN(AA:AA)
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "daniel chen" <danchen@worldnet.att.net> wrote in message
> > news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
> >> Is there any code to convert column(number) to column(letters), and V.V.
> >> e.g.  27 for AA,     AB for 28
> >> Thanks
> >>
> >

-- 

Dave Peterson
0
ec357201 (5290)
4/30/2005 10:32:16 PM
Hi Dave,
Thank you for the code.  I may be able to use it.

I have this code
Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)"
And I want to make this constant range("$AH$8:$AN$200") a variable at the 
beginning of the setup.
i.e.   AH (which is col(34)) = Function(Cells(1, 10))
        let's say  Function1(cells(1, 10))  = cells(1, 10) + 29
                        Function2(cells(1, 10))  = cells(1, 10) + 35
        If cells(1, 10) = 5    then,  Function1(cells(1, 10)) = 34   which 
is column("AH")
        Function2(cells(1, 10)) = 40   which is column("AN")
       AH   AN    depend on what is in cells(1, 10)

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:42740770.B5F614DF@netscapeXSPAM.com...
> How about:
>
> =SUBSTITUTE(ADDRESS(1,27,4),"1","")
>
>
>
> daniel chen wrote:
>>
>> Hi, Peo
>> Great! Thanks. How about the other way?
>> i.e.  27 for AA
>>
>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> news:%23MpiXScTFHA.2560@TK2MSFTNGP09.phx.gbl...
>> > =COLUMN(AA:AA)
>> >
>> > --
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> >
>> > "daniel chen" <danchen@worldnet.att.net> wrote in message
>> > news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
>> >> Is there any code to convert column(number) to column(letters), and 
>> >> V.V.
>> >> e.g.  27 for AA,     AB for 28
>> >> Thanks
>> >>
>> >
>
> -- 
>
> Dave Peterson 


0
danchen (87)
4/30/2005 11:31:18 PM
Hi Dave,
I have it working, but  I couldn't make the super formula to work.
Why is that?

Sub getdata()
Dim r As Integer
Dim c As Integer
Dim LR As Integer
Dim reg1 As Variant
    reg1 = Cells(1, 27).Value
    Range("AA2") = "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")"
    Range("AA3") = "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 & 
",4),""1"","""")"
For LR = 1 To 200
If Cells(LR, 1) = "" Then Exit For
Next LR
For r = 1 To LR - 1
For c = 2 To 7
'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$AH$8:$AN$200," & c & ",FALSE)" 
'orginal
Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & Range("AA2") & "$8:$" & 
Range("AA3") & "$200," & c & ",FALSE)"     ' it works

'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & "=SUBSTITUTE(ADDRESS(1," & 29 
+ reg1 & ",4),""1"","""")" & "$8:$" & "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * 
reg1 & ",4),""1"","""")" & "$200," & c & ",FALSE)"         ' does not work
Next c
Next r
Cells(1, 1).Select
End Sub

"daniel chen" <danchen@worldnet.att.net> wrote in message 
news:azUce.166855$cg1.54132@bgtnsc04-news.ops.worldnet.att.net...
> Hi Dave,
> Thank you for the code.  I may be able to use it.
>
> I have this code
> Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)"
> And I want to make this constant range("$AH$8:$AN$200") a variable at the 
> beginning of the setup.
> i.e.   AH (which is col(34)) = Function(Cells(1, 10))
>        let's say  Function1(cells(1, 10))  = cells(1, 10) + 29
>                        Function2(cells(1, 10))  = cells(1, 10) + 35
>        If cells(1, 10) = 5    then,  Function1(cells(1, 10)) = 34   which 
> is column("AH")
>        Function2(cells(1, 10)) = 40   which is column("AN")
>       AH   AN    depend on what is in cells(1, 10)
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
> news:42740770.B5F614DF@netscapeXSPAM.com...
>> How about:
>>
>> =SUBSTITUTE(ADDRESS(1,27,4),"1","")
>>
>>
>>
>> daniel chen wrote:
>>>
>>> Hi, Peo
>>> Great! Thanks. How about the other way?
>>> i.e.  27 for AA
>>>
>>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>>> news:%23MpiXScTFHA.2560@TK2MSFTNGP09.phx.gbl...
>>> > =COLUMN(AA:AA)
>>> >
>>> > --
>>> > Regards,
>>> >
>>> > Peo Sjoblom
>>> >
>>> >
>>> > "daniel chen" <danchen@worldnet.att.net> wrote in message
>>> > news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
>>> >> Is there any code to convert column(number) to column(letters), and 
>>> >> V.V.
>>> >> e.g.  27 for AA,     AB for 28
>>> >> Thanks
>>> >>
>>> >
>>
>> -- 
>>
>> Dave Peterson
>
> 


0
danchen (87)
5/1/2005 6:16:21 AM
I didn't realize you wanted to use a VBA routine...

dim reg1 as long
dim myRng as range

reg1 = activesheet.cells(1,27).value
with sht1
  set myrng = .range(.cells(1,reg1+29),.cells(200,30+2*reg1))
end with

'I'm confused about what you're doing, but maybe something like...

activesheet.cells(r,c).formula _
  = "=vlookup($A" & r & "," & myrng.address(external:=true) & "," _
            & c & ",false)"


  




daniel chen wrote:
> 
> Hi Dave,
> I have it working, but  I couldn't make the super formula to work.
> Why is that?
> 
> Sub getdata()
> Dim r As Integer
> Dim c As Integer
> Dim LR As Integer
> Dim reg1 As Variant
>     reg1 = Cells(1, 27).Value
>     Range("AA2") = "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & ",4),""1"","""")"
>     Range("AA3") = "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 &
> ",4),""1"","""")"
> For LR = 1 To 200
> If Cells(LR, 1) = "" Then Exit For
> Next LR
> For r = 1 To LR - 1
> For c = 2 To 7
> 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$AH$8:$AN$200," & c & ",FALSE)"
> 'orginal
> Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & Range("AA2") & "$8:$" &
> Range("AA3") & "$200," & c & ",FALSE)"     ' it works
> 
> 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & "=SUBSTITUTE(ADDRESS(1," & 29
> + reg1 & ",4),""1"","""")" & "$8:$" & "=SUBSTITUTE(ADDRESS(1," & 30 + 2 *
> reg1 & ",4),""1"","""")" & "$200," & c & ",FALSE)"         ' does not work
> Next c
> Next r
> Cells(1, 1).Select
> End Sub
> 
> "daniel chen" <danchen@worldnet.att.net> wrote in message
> news:azUce.166855$cg1.54132@bgtnsc04-news.ops.worldnet.att.net...
> > Hi Dave,
> > Thank you for the code.  I may be able to use it.
> >
> > I have this code
> > Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)"
> > And I want to make this constant range("$AH$8:$AN$200") a variable at the
> > beginning of the setup.
> > i.e.   AH (which is col(34)) = Function(Cells(1, 10))
> >        let's say  Function1(cells(1, 10))  = cells(1, 10) + 29
> >                        Function2(cells(1, 10))  = cells(1, 10) + 35
> >        If cells(1, 10) = 5    then,  Function1(cells(1, 10)) = 34   which
> > is column("AH")
> >        Function2(cells(1, 10)) = 40   which is column("AN")
> >       AH   AN    depend on what is in cells(1, 10)
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > news:42740770.B5F614DF@netscapeXSPAM.com...
> >> How about:
> >>
> >> =SUBSTITUTE(ADDRESS(1,27,4),"1","")
> >>
> >>
> >>
> >> daniel chen wrote:
> >>>
> >>> Hi, Peo
> >>> Great! Thanks. How about the other way?
> >>> i.e.  27 for AA
> >>>
> >>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> >>> news:%23MpiXScTFHA.2560@TK2MSFTNGP09.phx.gbl...
> >>> > =COLUMN(AA:AA)
> >>> >
> >>> > --
> >>> > Regards,
> >>> >
> >>> > Peo Sjoblom
> >>> >
> >>> >
> >>> > "daniel chen" <danchen@worldnet.att.net> wrote in message
> >>> > news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
> >>> >> Is there any code to convert column(number) to column(letters), and
> >>> >> V.V.
> >>> >> e.g.  27 for AA,     AB for 28
> >>> >> Thanks
> >>> >>
> >>> >
> >>
> >> --
> >>
> >> Dave Peterson
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
5/1/2005 11:34:36 AM
Hi Dave,
Truely grateful.
I am developing tools for analysis.
Try to conserve memories and time.

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message 
news:4274BECC.25054C14@netscapeXSPAM.com...
>I didn't realize you wanted to use a VBA routine...
>
> dim reg1 as long
> dim myRng as range
>
> reg1 = activesheet.cells(1,27).value
> with sht1
>  set myrng = .range(.cells(1,reg1+29),.cells(200,30+2*reg1))
> end with
>
> 'I'm confused about what you're doing, but maybe something like...
>
> activesheet.cells(r,c).formula _
>  = "=vlookup($A" & r & "," & myrng.address(external:=true) & "," _
>            & c & ",false)"
>
>
>
>
>
>
>
> daniel chen wrote:
>>
>> Hi Dave,
>> I have it working, but  I couldn't make the super formula to work.
>> Why is that?
>>
>> Sub getdata()
>> Dim r As Integer
>> Dim c As Integer
>> Dim LR As Integer
>> Dim reg1 As Variant
>>     reg1 = Cells(1, 27).Value
>>     Range("AA2") = "=SUBSTITUTE(ADDRESS(1," & 29 + reg1 & 
>> ",4),""1"","""")"
>>     Range("AA3") = "=SUBSTITUTE(ADDRESS(1," & 30 + 2 * reg1 &
>> ",4),""1"","""")"
>> For LR = 1 To 200
>> If Cells(LR, 1) = "" Then Exit For
>> Next LR
>> For r = 1 To LR - 1
>> For c = 2 To 7
>> 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$AH$8:$AN$200," & c & ",FALSE)"
>> 'orginal
>> Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & Range("AA2") & "$8:$" &
>> Range("AA3") & "$200," & c & ",FALSE)"     ' it works
>>
>> 'Cells(r, c) = "=VLOOKUP($A" & r & ",Sht1!$" & "=SUBSTITUTE(ADDRESS(1," & 
>> 29
>> + reg1 & ",4),""1"","""")" & "$8:$" & "=SUBSTITUTE(ADDRESS(1," & 30 + 2 *
>> reg1 & ",4),""1"","""")" & "$200," & c & ",FALSE)"         ' does not 
>> work
>> Next c
>> Next r
>> Cells(1, 1).Select
>> End Sub
>>
>> "daniel chen" <danchen@worldnet.att.net> wrote in message
>> news:azUce.166855$cg1.54132@bgtnsc04-news.ops.worldnet.att.net...
>> > Hi Dave,
>> > Thank you for the code.  I may be able to use it.
>> >
>> > I have this code
>> > Cells(1, 2) = "=VLOOKUP($A1,Sheet1!$AH$8:$AN$200,2,FALSE)"
>> > And I want to make this constant range("$AH$8:$AN$200") a variable at 
>> > the
>> > beginning of the setup.
>> > i.e.   AH (which is col(34)) = Function(Cells(1, 10))
>> >        let's say  Function1(cells(1, 10))  = cells(1, 10) + 29
>> >                        Function2(cells(1, 10))  = cells(1, 10) + 35
>> >        If cells(1, 10) = 5    then,  Function1(cells(1, 10)) = 34 
>> > which
>> > is column("AH")
>> >        Function2(cells(1, 10)) = 40   which is column("AN")
>> >       AH   AN    depend on what is in cells(1, 10)
>> >
>> > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
>> > news:42740770.B5F614DF@netscapeXSPAM.com...
>> >> How about:
>> >>
>> >> =SUBSTITUTE(ADDRESS(1,27,4),"1","")
>> >>
>> >>
>> >>
>> >> daniel chen wrote:
>> >>>
>> >>> Hi, Peo
>> >>> Great! Thanks. How about the other way?
>> >>> i.e.  27 for AA
>> >>>
>> >>> "Peo Sjoblom" <terre08@mvps.org> wrote in message
>> >>> news:%23MpiXScTFHA.2560@TK2MSFTNGP09.phx.gbl...
>> >>> > =COLUMN(AA:AA)
>> >>> >
>> >>> > --
>> >>> > Regards,
>> >>> >
>> >>> > Peo Sjoblom
>> >>> >
>> >>> >
>> >>> > "daniel chen" <danchen@worldnet.att.net> wrote in message
>> >>> > news:eRRce.675942$w62.535613@bgtnsc05-news.ops.worldnet.att.net...
>> >>> >> Is there any code to convert column(number) to column(letters), 
>> >>> >> and
>> >>> >> V.V.
>> >>> >> e.g.  27 for AA,     AB for 28
>> >>> >> Thanks
>> >>> >>
>> >>> >
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >
>> >
>
> -- 
>
> Dave Peterson 


0
danchen (87)
5/1/2005 9:40:07 PM
Reply:

Similar Artilces:

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Any XML Naming Conventions?
Does the use of DTD, XML Schema and similar constructs adopt the use of C# naming conventions? If so how do I make the distinction of how to apply C# conventions with XML elements, attributes and so on? Any referrals to resources that discuss or document XML Naming Conventions? -- <%= Clinton Gallagher, "Twice the Results -- Half the Cost" Architectural & e-Business Consulting -- Software Development NET csgallagher@REMOVETHISTEXTmetromilwaukee.com URL http://www.metromilwaukee.com/clintongallagher/ clintonG wrote: > Does the use of DTD, X...

Change of recipient/sender display name
Hi All, Environment :- Windows 2003 x64 with exchange 2007 (All in one role) Outlook client :- 2003, 2007 We have created a list of mail contacts (which under GAL) in exchange 2007, these mail contacts are associates with external email address, each of contact has a naming standard for display name, for example, display name is HD Finance Manager, external email address is fin_manager@hdemea.com I want to configure outlook client to show the display name according to mail contacts whenever the email address is match to mail contacts list (GAL), for example, an email sent from the email a...

Not allow entering repeated references in a column
Frank, If you don�t mind ... I will send it :) . As I don�t know your e-mail, I will give mine, which is ssouritinha@sapo.pt Just send your e-mail to my e-mail box and I will send the file. Thanks, Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

cell looses name after sorting
Can someone help me with the following problem in Excel 2000: in a table I have attached serveral cells with unique cell names, the values in these cells are used in other sheets. the problem is that when I sort the table, the cell names stay in the original rowposition; they are not sorted! while their values are. So Cell names get different values, and other calculations on my other sheets get messed up! How can I make the cell names relative instead of absolute? thankx in advance, Jim --- Message posted from http://www.ExcelForum.com/ "jimfx >" <<jimfx.109zcv@exc...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Can the column index in a cell address be made variable?
Hi, To refer to a cell with a variable row number, we can just code it as, for example, Dim i As Interger i=234 Range("A" & i).Select To refer to a cell with a variable column index, it seems not that easy because the column index must be explicitly specified in a cell address. So, if I want to go to the j th column on the 2nd row or j columns to the right of cell AA3, is there a quick and easy way to do it? Thank you in advance. David You may be able to use R1C1 terminology, but you'll need to get someone else to help from here. "cyberdude" <honc...

Splitting First and Last Names
I have a spreadsheet where the Name feild contains both first and last names. I want to split this into two fields. How can I do this? Hi use 'Data - Text to<columns' for this -- Regards Frank Kabel Frankfurt, Germany Jamie wrote: > I have a spreadsheet where the Name feild contains both first and last > names. I want to split this into two fields. How can I do this? "Jamie" <pastorjman310@hotmail.com> wrote >I have a spreadsheet where the Name feild contains both >first and last > names. I want to split this into two fields. How can I do &g...

Exporting file names to an excel spreadsheet.
I have a folder containing many many many .pdf files. Is there a way to export the filenames of these .pdf files into an excel spreadsheet? If this is a dumb question, I apologize. I didnt have any luck searching for the answer here. -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30611 View this thread: http://www.excelforum.com/showthread.php?threadid=547520 I use Jim Cone's fine Add-in called "ListFiles"for this sort of thing. It's available free (a...

Subform in Datasheet View
Is there a way to have the datasheet view of a subform *not* show the "button-like" row that shows the field names? -- Thanks, tbl No there isn't. If you create a continuous style form, you can eliminate the headings. You can modify a continuous form so that it looks like a 'datasheet'. -- Joan Wild Microsoft Access MVP "tbl" <hateThatSpam@invalid.net> wrote in message news:4gqs13dds31nhu1bsmbrm5tt86adbfea1m@4ax.com... > Is there a way to have the datasheet view of a subform *not* > show the "button-like" row that shows the field n...

Comparing first and last names in two lists
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

Change License / Company name
Hi, we tried CRM 1.2 with the MSDN license key. Now my company bought a 25 user Proffesional Suite license and I want to change the key on the production server. Is there an easy way to do this?? The only solution I see, is via the Data Migration Framework. Erik. How about the re-deploy tool? "Erik" <none> wrote in message news:u0SXdbXlEHA.3452@TK2MSFTNGP15.phx.gbl... > Hi, > we tried CRM 1.2 with the MSDN license key. Now my company bought a 25 > user > Proffesional Suite license and I want to change the key on the production > server. Is there an easy wa...

what is the name of a sub form download from microsoft website
i need to add a sub form to a form created in access. i tried to start the wizard but the feature is not currently installed. i try to install but it is looking for a disk for me to insert. i do not have a disk to insert. can i go to microsoft website and download the sub form feature for access? if yes, what is the name of the download. Are you sure it is not installed? Open your main form in design view. Click the Toolbox button in the menu at the top. There are two buttons at the top of the Toolbox, an Arrow and a wand. Is the Wand highlighted. If not, click on it then click on th...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

combining columns all the way down
I am trying to combine two columns of information in excel but th concatenate function doesnt seem to work for it. It may just be m ineptitude in excel but I just cant seem to figure this out. I have 3 colums Column A__________Column B___________Column C tree_______________ .jpg dog________________.gif House______________.png But I cant seem to be able to make it so that column C has tree.jpg an dog.gif and house.png Is concatenate the wrong thing to use here? or am I making my formula incorrectly -- sparkrom ----------------------------------------------------------------------- sparkro...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...

Look up column name, match rows (a/cnumber) & summing up....its confusing!
Hi All, Here is what I'v been trying to do but..! I'v 2 sets of data (data1 & data2) Column name may be sane/different in each data set and same applies to acct_no.. I want to prepare a report that combine product & accounts data show accumulatd result on another sheet/file. Data 1 - upto Jan 2006 Acct. No Total Prod - A Prod - B Prod-C 1001 51 10 30 11 1002 47 15 20 12 1003 80 20 15 45 1004 64 25 16 23 Total 242 70 81 91 Data 2 - Feb 2006 Acct. No Total Prod - A Prod - B 1002 7 5 2 1004 16 10 6 1009 9 3 6 Total 32 18 14 Report required Acct upto Jan'06 Prod - ...

name badge labels
Now we have Office 2007 and I cannot figure out how to create a sheet of name badge labels. Are you using mail merge? If you are, select the your label from the page setup. If you are manually typing your badge labels, measure your labels and setup your page with grid guides. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Carol in Texas" <CarolinTexas@discussions.microsoft.com> wrote in message news:626CC158-3AF1-4809-9BCE-C54E1FF42861@microsoft.com... > Now we have Office 2007 and I cannot figure out how to...

appearance of the real name in "sent"
I posted a message yesterday but nobody answered. So I try to do it again. I cannot find the answer to this question in help (OL 2000). I setup my contact list, with the names, the way I want them to appear, the email address etc... When I want to send an email, I click on the contact I want, action, etc... When it is sent, it appears with the email of the person. For example, I would like "John" to appear, and I get (and he too) say.. john@msn.com. It would be more friendly, and more polite and I know it can be done, as I receive some to my name. anonymous@discussions.m...

Import excel named range into Access error 3011
Hi, I've read a few posts on this topic, but none addresses my particular problem. I use Office 2003 on an XP SP3 machine. I am using transferspreadsheet to import a named range in an excel file into Access. This has worked beautifully until suddenly it stopped working. I find now that if I extract the address of the named range from within the Access VBA code, it looks like this: =#REF!$A$1:$Z$166 rather than this, which is what it should be: =Data!$A$1:$Z$166 When I open the spreadsheet and get the address of the named range in the immediate window, it also has the #REF error....

OWA using mix of machine name/FQDN, screwing things up with HTTPS
Hello, We have one particular customer whose SBS 2003 box is giving me some heartburn when it comes to setting up OWA. I've set it up for HTTPS only, and setup permissions the exact same as our other many working OWA systems. Here's what happens when I try and hit the "broken" one: When I go to: https://server.domain.com/exchange, I get prompted for a username and password, which works properly. After login, I get a warning that the page contains a mix of both secure and non secure items. If I don't display the non secure items, I see the two "panes"...

I need a formula to sum column b if column a is between two dates
I have an excel spreadsheet with employees time off. I need a formula that will add column b if column a is betwee two dates. For example: if column a is between 9/22/04 and 9/21/05 then add column b. I have tried all different formluas but can't get this to work. ...