How do I get this option avaliable?

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Move forumlas to another sheet but keep references to first sheet I need to copy a section of one sheet to another. The section contains formulas referring to the same sheet. When pasted into the second, the formulas should refer to the same cells on the first sheet. If I copy a formula with relative references e.g. =sum(A1:A5), it is pasted as =sum(#ref!). If I paste a formula with an absolute reference, e.g. it is pasted as the same absolute reference e.g. =sum($A$1:$A$5) which refers to the current sheet, not the originating sheet. What I need it to be is =SUM(Sheet1!A1:A5) How do I do this? Tag: How do I get this option avaliable? Tag: 107994
  - 2
    - Further Show Hide Problem with VBA Hi again! Bob kindly gave me a suggestion for using VBA to show /hide using a single button. This worked fine until I decided I wanted to have another custom area off to the side. The problem being that when I try to return from this third view not all rows columns are showing. I thought I might get round this by including an unhide for all rows and columns to create a normal view to go from but then the toggle views don't seem to do anything. Any help greatly appreciated DonH Call Unl Application.ScreenUpdating = False Rows("25:75").EntireRow.Hidden = Not Rows("25:75").EntireRow.Hidden Rows("98:148").EntireRow.Hidden = Not Rows("98:148").EntireRow.Hidden Rows("244:294").EntireRow.Hidden = Not Rows("244:294").EntireRow.Hidden Rows("317:367").EntireRow.Hidden = Not Rows("317:367").EntireRow.Hidden Range("K4").Select Calculate Call Pro -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DonH" <d.hart440@ntlworld.com> wrote in message news:WIB4h.25197$Ib.15825@newsfe1-gui.ntli.net... > Hi hope you can help. > > I know next to nothing about VBA and have adapted code below from stuff I > had. > > I simply want to show hide portions of my page to cut down on screen > clutter/information overload. > > I have assigned my macro to a screen image but was wondering if I could have > a hide and unhide under the same button. > > Any help would be much appreciated > > > > Call Unl > Application.ScreenUpdating = False > Rows("25:75").EntireRow.Hidden = True > Rows("98:148").EntireRow.Hidden = True > Rows("171:221").EntireRow.Hidden = True > Rows("244:294").EntireRow.Hidden = True > Rows("317:367").EntireRow.Hidden = True > Range("K4").Select > Calculate > Call Pro > > > DonH > > Tag: How do I get this option avaliable? Tag: 107993
  - 3
    - display data form chosen record of a list Hello there! I'm a little newbie to excel and i want to do something with some records. I have some lists of records in a sheet for example cities Athens Kavala Thessaloniki ---------- ---------------- ---------------- ---------------------- Athens 15 35 89 Kavala 26 85 90 Thessaloniki 89 23 34 I want a list in a new sheet that will contain all the records form cities and when i chose a record for example Athens, to display on the cell below all the records that "Athens" contains : 15,26,89. I have made the lists but i dont know how to display the data from every list when i make the selection. Does anyone know how to do this and is this possible???? Tag: How do I get this option avaliable? Tag: 107991
  - 4
    - Charttitle.Text crashes (Excel 2000) Hi, when I try to run the following code (on an existing diagram): With ActiveChart Dim globalLstFile as String globalLstFile =3D "02 KOBA_XYZ_C3-06214 02- 20.7-20.8=B0C Touchprobe Mittelpunkte.lst" =2EHasTitle =3D True =2EChartTitle.Text =3D "KOBA Sphere Gauge S/N 38 538 (" + globalLstFile + ")" & Chr(10) & _ "color coding: red =3D Zone 1, green =3D Zone 2, blue =3D Zone 3"= & Chr(10) & _ "light color: measurements that contain spheres that are within 20 cm of the edge of the measurement volume" .=2E. Excel returns with the error 1004. However, if I set globalLstFile to "test", then the code runs just fine. I did not find any limitation on .ChartTitle.Text, but I am suspecting that it is limitied to a certain number of characters or does not allow special characters. Any enlightment would be greatly appreciated (sorry for the horrible formatting of this text). Blackno666. Tag: How do I get this option avaliable? Tag: 107988
  - 5
    - Hyperlink from Different File to Specified Worksheet I have 2 Files (File1.xls and File2.xls). Both files have 3 worksheets. How do I create hyperlink from File1 to File2's Sheet2 worksheet? Hope you might help. Tag: How do I get this option avaliable? Tag: 107984
  - 6
    - VBA using an image as a toggle switch Hi hope you can help. I know next to nothing about VBA and have adapted code below from stuff I had. I simply want to show hide portions of my page to cut down on screen clutter/information overload. I have assigned my macro to a screen image but was wondering if I could have a hide and unhide under the same button. Any help would be much appreciated Call Unl Application.ScreenUpdating = False Rows("25:75").EntireRow.Hidden = True Rows("98:148").EntireRow.Hidden = True Rows("171:221").EntireRow.Hidden = True Rows("244:294").EntireRow.Hidden = True Rows("317:367").EntireRow.Hidden = True Range("K4").Select Calculate Call Pro DonH Tag: How do I get this option avaliable? Tag: 107983
  - 7
    - How to create "frames" in Excel 2003? Hi all, Is there a way to create frames in Excel like one can do for webpages? For example if I split my sheet vertically, I want a frame on the left side which has "links" that if I click them change the sheet I'm viewing on the right side. Thank you! Tag: How do I get this option avaliable? Tag: 107978
  - 8
    - Generating XML from Excel2002 using XSD Hi I understand that XL2003 offers nice XML features that could do what I want easily. But I am not sure about what XL2002 Pro can let me do. I need to generate pretty basic XML in Excel 2002 VBA. I have an idea of my XSD schema - say Author --> [Name -once ]/[Email- unbounded]l/[Book -unbounded --> Title/Publisher/ISBN] Data comes from Excel Can you give a basic code of how I should use VBA in conjunction of the XSD file I have access to msxml and msxml4 DLLs Thanks a lot Ben Tag: How do I get this option avaliable? Tag: 107977
  - 9
    - Filtering Hi to all, I have worksheet nammed HCP2006. colomn EK has numbers in some of it's cells. The code below is to select only the rows that has numbers (not blank) in colomn EK starting from row 6. These filtered rows has to be copied to another worksheet in the same folder that has the name WV_KP_06 which containes 12 sheets for 12 months and according to the month (here it is June). When cells in colomn EK is empty (all blank) an error occures the code does not continue. I want to add a Message box telling that there is nothing to be filtered and to return every thing as it was before starting the code. Note: this code is run when pressing a button on the HCP_2006 worksheet. Thanks in advance, Khalil Handal Here is the code: Sub KP6() ' Month of June06 KP Dim RngToFilter66 As Range Dim RngToCopy66 As Range Dim Destwks66 As Worksheet Dim DestCell66 As Range Dim LastRow66 As Long With ActiveSheet .Unprotect Password:="1230" 'turn off any existing filter .AutoFilterMode = False Set RngToFilter66 = .Range("EK6", .Cells(.Rows.Count, "EK").End(xlUp)) RngToFilter66.AutoFilter Field:=1, Criteria1:="<>" If RngToFilter66.Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then 'no visible rows in filter. Set RngToCopy66 = Nothing Else With RngToFilter66 Set RngToCopy66 = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _ .Cells.SpecialCells(xlCellTypeVisible) End With End If .AutoFilterMode = False .Protect Password:="1230" End With Set Destwks66 = Nothing On Error Resume Next Set Destwks66 = Workbooks("wv_KP_06.xls").Worksheets("Jun") On Error GoTo 0 If Destwks66 Is Nothing Then Set Destwks66 = Workbooks.Open(ThisWorkbook.Path & "\WV_KP_06.xls").Worksheets("Jun") End If With Destwks66 ' delete any previous lines after row 7 Worksheets("Jun").Select Rows("7:50").Select Selection.ClearContents Range("A7").Select ' previous line added by me LastRow66 = .Cells(.Rows.Count, "B").End(xlUp).Row + 1 Set DestCell66 = .Cells(LastRow66, "A") End With RngToCopy66.EntireRow.Copy _ Destination:=DestCell66 Application.CutCopyMode = False End Sub Tag: How do I get this option avaliable? Tag: 107976
  - 10
    - Counting cells Hi, I had this before, but it didn't work weel! I may have not made it clear. Range C5:C29 containes either letter C or letter M. Range D5:D29 containes either letter M or letter F line 5 C F line 6 C M line 7 M M .......... In cell D34 I want to count the number of occurance of the combination of C and M. The array formula =COUNTIF(C5:C29,"C") counts the value of colomn C. What adjustment do I need to do to include the values in colomn D? I tried this: =COUNTIF(C5:C29,"C"), (D5:D29,"M") but had an error Khalil Tag: How do I get this option avaliable? Tag: 107972
  - 11
    - Counting cells Hi, I had this before, but it didn't work weel! I may have not made it clear. Range C5:C29 containes either letter C or letter M. Range D5:D29 containes either letter M or letter F line 5 C F line 6 C M line 7 M M .......... In cell D34 I want to count the number of occurance of the combination of C and M. The array formula =COUNTIF(C5:C29,"C") counts the value of colomn C. What adjustment do I need to do to include the values in colomn D? I tried this: =COUNTIF(C5:C29,"C"), (D5:D29,"M") but had an error Khalil Tag: How do I get this option avaliable? Tag: 107971
  - 12
    - Reference Grouped Objects Using VBA Hello I have grouped four charts, three text boxes (from the control toolbox) and a rectangle. Prior to grouping, the code I had to update the axes on both charts and text for the text boxes worked fine. After grouping the objects the code fails. How do you reference a specific object contained in a group? Tag: How do I get this option avaliable? Tag: 107960
  - 13
    - excel time calculation I have a cell (E9) that has calculated the hours worked 'D9-C9(D9>C9)' and an adjacent cell (F9) to convert this to decimal ('E9*24'). If there is no entry in C9 and D9, F9 reads '-24'. How do I correct this to show 0.00? Tag: How do I get this option avaliable? Tag: 107958
  - 14
    - Formula for 3 columns Example as to a formula I need Add Subtract Balance Column C Column I Column J $1000 -$500 =$500 All columns are set up to start with row C14 and finish with C100 I column starts with row I14 and finish with I 100 J column starts with row J14 and finish with J100 Columns C & I presently have amounts in each cell. Where there are no actual amounts, I have $0.00 to fill cell Column J has no figures at all except all cells have $0.00 Could someone please create a formula for me that would give me a continous running balance in Column J based on the above information -- Thank you in advance for your assistance HankL Tag: How do I get this option avaliable? Tag: 107956
  - 15
    - I"M STUCK! help please! here is what i want to do: 2 columns: A & B col A: list of months (12): cell A1:jan/06, A2: feb/06, A3: mar/06, A4: apr/06, A5:...... A12: dec/06 col B: list of sales that correspond to the month (only 4): B1:100, B2:112, B3:105, B4:112 I want a formula that will tell me the latest month that the sales is inputed (in this case B4) = apr/06 i've gotten the formula to find the last number of the set: which give me the number of "112" (i put this formula in D1) =INDEX(A1:A12,MATCH(9.99999999999999E+307,B1:B12)) or =LOOKUP(9.99999999999999E+307,B1:B12) but where i'm stuck at is using index/match, (i put this formula in E1) =INDEX($A$1:$A$12,MATCH(D1,$B$1:$B$12,0)) and in the example above, it would always return to me (feb/06) as it is the same sales figure as apr/06, but I want the formula to to tell me apr/06! i've also tried changing the match type to '1' and it did do anything. i feel i'm so close but cn't figure it out! Tag: How do I get this option avaliable? Tag: 107948
  - 16
    - I"M STUCK! help please! here is what i want to do: 2 columns: A & B col A: list of months (12): cell A1:jan/06, A2: feb/06, A3: mar/06, A4: apr/06, A5:...... A12: dec/06 col B: list of sales that correspond to the month (only 4): B1:100, B2:112, B3:105, B4:112 I want a formula that will tell me the latest month that the sales is inputed (in this case B4) = apr/06 i've gotten the formula to find the last number of the set: which give me the number of "112" (i put this formula in D1) =INDEX(E17:E28,MATCH(9.99999999999999E+307,B1:B12)) or =LOOKUP(9.99999999999999E+307,B1:B12) but where i'm stuck at is using index/match, (i put this formula in E1) =INDEX($A$1:$A$12,MATCH(D1,$B$1:$B$12,0)) and in the example above, it would always return to me (feb/06) as it is the same sales figure as apr/06, but I want the formula to to tell me apr/06! i've also tried changing the match type to '1' and it did do anything. i feel i'm so close but cn't figure it out! Tag: How do I get this option avaliable? Tag: 107947
  - 17
    - vlookup problem Excel 2002 SP3 Win XP HE SP1 *Follow-up group is: microsoft.public.excel* *only reply to: microsoft.public.excel* hi, i am having a problem with vlookup wherein if i use formula A: =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't: =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE) the only difference is the table array range which in the formula B looks into the same sheet etc., but at more columns. i can't understand what is causing the problem. i believe that the columns do not need to be sorted (help file); the sheets are formatted properly, etc. i've done LOTS of vlookups in really complex spreadsheets in the past but am rusty right now and can't put my finger on what i am doing wrong. anyone can help? Tag: How do I get this option avaliable? Tag: 107943
  - 18
    - Finding missing numbers in a list I have a series of client account numbers that range from 001-999. I first imported the client information from Timeslips which where we keep client information into an Excel spreadsheet. What I need to now do is create a macro or a query that will tell me what numbers are not being utilized from the spreadsheet that I created but I don't know how to do this. I have only created one macro before and that was with help from an instructor so I don't know where to begin can someone please help me with this. Lynne Tag: How do I get this option avaliable? Tag: 107933
  - 19
    - checkbox linked to a cell Hello, I am programatically trying to add a checkbox. but it looks like you have to give pixal locations ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _ DisplayAsIcon:=False, Left:=111.75, Top:=197.25, Width:=85.5, Height _ :=21.75).Select is there a way to add a checkbox and give the cell row / column? Tag: How do I get this option avaliable? Tag: 107932
  - 20
    - Newbie Question open a web service in excel Ok here is where I am at.... I have created a web service in vs 2005. I have tested it and have been able to bind the content to a gridview....... below is my code: using System.Data.SqlClient; /// <summary> /// Summary description for dbconn /// </summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] public class dbconn : System.Web.Services.WebService { [WebMethod] public DataSet GetTitleAuthors() { SqlConnection myConnection = new SqlConnection("Data Source=JEREMY1;Initial Catalog=pubs;User ID=sa;Password=beth123"); SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from Authors", myConnection); // SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Titles", myConnection); DataSet ds = new DataSet(); myCommand1.Fill(ds, "Authors"); // myCommand2.Fill(ds, "Titles"); return (ds); } I have downloaded the "Microsoft Offiec 2003 Web Service" a have installed it..... I am able to add the web service and it creates the necesary file In excel I create a button and add the following code Dim wsAuthors As New clsws_dbconn Dim value As String value = wsAuthors.wsm_GetTitleAuthors() To be honest I am not sure how to return the 23 records that are in the ds to the excel grid... any help or bump in the right direction would be helpful Tag: How do I get this option avaliable? Tag: 107931
  - 21
    - graphiques comment eviter sur un graphique mensuel que la courbe ne retombe a zero les jours ou la plage de données n'est pas renseigne et que le dernier point a valeur rejoigne le suivant ? merci d'avance Tag: How do I get this option avaliable? Tag: 107930
  - 22
    - HELP WITH FORMULA I have 3 columns. C -I=J These columns are continuous and I keep adding amounts to cells in column C and deducting amounts from I column and need to end up with a total in column J. Could someone please help me with a formula that will continually give me a total to column J as I enter information into column C and deducting from column I. -- Thank you in advance for your assistance HankL Tag: How do I get this option avaliable? Tag: 107926
  - 23
    - IF Statement, Group Adding, Please help me I have a problem in using an If Statement and adding statement together. A B C D Carl 1 2 3 1 2 Luis 1 1 11 Jim 1 1 1 I am trying to make an if statement where if there is anything as in letters or words in column A, it will add whatever is in cell B and C. So the 2nd row plus the 3rd row plus the last row will equal to 7. Since the 3rd row has nothing in column A, it will not add C's value. I would need the answer in one cell that adds up the whole result to 7. Thanks for the help. Tag: How do I get this option avaliable? Tag: 107917
  - 24
    - Generating an EXCEL dataset with formating I have the need to generate an EXCEL dataset, not macro driven, which has formatting that just can?t be done in the CSV type of file format. Generally my requirements are for a 6 column table with bold column headings and a title line above the column heading that is also bold and in a merged cell of the 6 columns. Currently I am generating a .XML formatted document, but you have to remember to say Open with EXCEL or it opens in the browser and you see the code to generate the table not the table. The beginning of the code is below. Is there a better format that can be used, so EXCEL is the default format? Also where is the ?description of the format at? The dataset is being sent as an attachment to people, so I can?t control their machines and change the file associations. <?xml version="1.0"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>Kenneth Leidner</Author> <LastAuthor>Me</LastAuthor> <Created>2006-11-02T10:03:38Z</Created> <LastSaved>2006-11-02T10:03:38Z</LastSaved> <Company>XYZ</Company> <Version>10.6735</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <DownloadComponents/> <LocationOfComponents HRef="file:///\\"/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>7935</WindowHeight> <WindowWidth>15195</WindowWidth> <WindowTopX>0</WindowTopX> <WindowTopY>45</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s21"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> <NumberFormat ss:Format="0000"/> </Style> <Style ss:ID="s22"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> </Style> <Style ss:ID="s23"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> <Font x:Family="Swiss" ss:Bold="1"/> </Style> <Style ss:ID="s24"> <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/> <Font x:Family="Swiss" ss:Bold="1"/> </Style> <Style ss:ID="s25"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/> <Font x:Family="Swiss" ss:Bold="1"/> <NumberFormat ss:Format="0000"/> </Style> </Styles> <Worksheet ss:Name="Book1"> <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="265" x:FullColumns="1" x:FullRows="1"> <Column ss:Index="2" ss:StyleID="s21"/> <Column ss:StyleID="s22" ss:Span="3"/> <Row> <Cell ss:MergeAcross="5" ss:StyleID="s23"><Data ss:Type="String">The title line for the month of Oct</Data></Cell> </Row> <Row> <Cell ss:StyleID="s24"><Data ss:Type="String">C1</Data></Cell> <Cell ss:StyleID="s25"><Data ss:Type="String">C2</Data></Cell> <Cell ss:StyleID="s23"><Data ss:Type="String">C3</Data></Cell> <Cell ss:StyleID="s23"><Data ss:Type="String">C4 #</Data></Cell> <Cell ss:StyleID="s23"><Data ss:Type="String">C5</Data></Cell> <Cell ss:StyleID="s23"><Data ss:Type="String">C6</Data></Cell> </Row> <Row> <Cell><Data ss:Type="Number">1</Data></Cell> <Cell><Data ss:Type="Number">0597</Data></Cell> <Cell><Data ss:Type="Number">06283</Data></Cell> <Cell><Data ss:Type="Number">02510</Data></Cell> <Cell><Data ss:Type="String">CAY448</Data></Cell> <Cell><Data ss:Type="String">T0971A</Data></Cell> </Row> And repeat for each row. Then there is some closeing commands for the table and all at the end. ? Tag: How do I get this option avaliable? Tag: 107913
  - 25
    - Output to excel Using standard DoCmd.OutputTo syntax to export a simple dataset to an Excel worksheet. Works well, but...... we have a list of text codes (YD, 2M, 7G, etc) which come across into the cells ok, all except codes like '11A', '10A' and the come into Excel as eg '0.458333333', '0.416666666666667' Reformatting as text/general has no effect at all Is there something I'm missing about the output process which would cure this quirk? Any ideas gratefully received TIA Steve Tag: How do I get this option avaliable? Tag: 107912
- Next
  - 1
    - Inconsistent formula evaluation in excel I have come across a somewhat frustrating problem in Excel 2002 when using if statements and then an evaluation which uses indirect addressing and match statements to perform an evaluation (subject to some preconditions in the if statement). My evaluation statement [MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),(B6:indirect(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6)))),0),COLUMN(E6))))] works fine on its own, and works equally well inside a single if but, as soon as I put two or more nested if statements before the evaluation, Excel sates that there is an error in the formula and will only accept the formula once I have replaced the indirect(address()) statements in the match statement with fixed addresses (which sort of defeats the whole point). I am using this rather convoluted formula as I have a real world data set where occasionally data is completely missing and sometimes individual data points are blank but I need to perform an evaluation over a fixed time span - the indirect addressing therefore defines the range I need to act upon, which varies, as I say, over a large data set (perhaps 34000 rows). What I really cannot understand is why, when my evaluation statement works fine, and when it works fine as the final part of a simple if, what Excel might be objecting to when I put it as the final part of a 3 level deep nested if... I have checked and quadruple checked that the syntax is correct and that all brackets are matched and correct, etc, but cannot find any error in the formula and cannot find a way of getting Excel to accept my formula entry. For info, her is a simple version that works followed by a simple version that does not - although they nominally do exactly the same thing! 1 works [=IF(OR(E6="",E101=""),"",(MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6))),0),COLUMN(E6))))))] 2 doesn't work [=IF(E6="","",if(E101="","",MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6))),0),COLUMN(E6))))))] Any help or advice would be greatly appreciated. Many thanks and best wishes Tag: How do I get this option avaliable? Tag: 107911
  - 2
    - Looking for pattern or formula I have a set of data that is ranked. Based on the current rank and the desired rank, I would like to call upon a formula that will project changes to the input factors to obtain the desired rank. Anyone know what that formula or pattern is? Thanks A bit more explanation I would like to compute changes to base rate, factor1, factor2 to get company A to rank 2 desired rank for company A= 2 Company B-D are fixed and their result will not change. Factor 1 is the representation of territory and Factor 2 is the representation of class Table Layout Base Rate Factor 1 Factor 2 Result Rank Company Territory Class 100 0.5 0.78 39 1 A 1 1 41 3 B 42 4 C 40 2 D 100 0.5 0.76 38 1 A 1 2 60 4 B 38 2 C 56 3 D 100 0.5 0.65 33 1 A 1 3 38 3 B 39 4 C 36 2 D 100 0.5 0.32 16 1 A 1 4 25 4 B 17 2 C 18 3 D 100 0.5 0.5 25 1 A 1 5 31 4 B 29 3 C 26 2 D 100 0.65 0.78 51 1 A 2 1 59 4 B 52 2 C 58 3 D 100 0.65 0.76 49 1 A 2 2 56 4 B 52 2 C 53 3 D 100 0.65 0.65 42 1 A 2 3 53 3 B 44 2 C 53 4 D 100 0.65 0.32 21 1 A 2 4 36 4 B 26 3 C 23 2 D 100 0.65 0.5 33 1 A 2 5 37 4 B 36 2 C 36 3 D Tag: How do I get this option avaliable? Tag: 107910
  - 3
    - Defining a table in Excel I have column headings for several columns of data that I'd like to treat as an Excel Table, not a pivot table, just a standard Excel table. What steps does one take to define a block of data as a table? Is it done by simply naming it? My thanks for any assistance here. Brad Tag: How do I get this option avaliable? Tag: 107907
  - 4
    - Hyperlinks Is there a way in excel to turn off the hyperlink feature for an entire column of data? I have a column of email addresses, and I don't want any of them to be an active hyperlink. I can remove the hyperlink individually, but have not been able to find a way to do it for the entire column of data. Thanks! Tag: How do I get this option avaliable? Tag: 107906
  - 5
    - Duplicate Values I have a spreadsheet that has 3000 entries and I have a coloum called SBS_No only problem is that I think I have duplicate entries as the SBS_No is unique. Is there anyway that I could get excel to point out any duplicate values. Like returning value false if it is a duplicate value. Fiona Tag: How do I get this option avaliable? Tag: 107905
  - 6
    - HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C7032B.0406A590 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! Sheet 1 contains the raw data. Sales and cost of sales for customers.=20 Sheet 2 contains the pivot table that summarizes the data in sheet 1.=20 In Sheet 3(a further summary of the Pivot Table), I would like to put in = the customer name and extract the related sales and cost of sales = numbers from the pivot table. How can I extract the desired information? Regards SJ ------=_NextPart_000_0006_01C7032B.0406A590 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3D"Arial Narrow" size=3D2>Hello!</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>Sheet 1 contains the raw data. = Sales and=20 cost of sales for customers. </FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>Sheet 2 contains the pivot = table that=20 summarizes the data in sheet 1. </FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>In Sheet 3(a further summary = of the Pivot=20 Table), I would like to put in the customer name and extract the = related=20 sales and cost of sales numbers from the pivot table. </FONT><FONT=20 face=3D"Arial Narrow" size=3D2>How can I extract the desired=20 information?</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>Regards</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>SJ</FONT></DIV></BODY></HTML> ------=_NextPart_000_0006_01C7032B.0406A590-- Tag: How do I get this option avaliable? Tag: 107904
  - 7
    - Importing External Data - Error Only One Column Imports I want to import data from on excel spreadsheet to another. I have been using the Import External Data feature with no issues. Currently, I am attempting to import an excel spreadsheet that is only about 20 columns in length and about 250 to 500 rows. When I complete the the import command it ONLY imports the first column and nothing else. Does anyone have any suggestions, insights, or comments on what could be causing the problem and what some poosible solutions may be. Thank you Ed Tag: How do I get this option avaliable? Tag: 107903
  - 8
    - Drop Down Lookup Return Value From Another Cell Greetings! I would like to have a drop down pick list that returns the value from an adjacent cell instead of the one picked. For example, I have a list of Units of Measures in one column and their abbreviations in an adjacent column (i.e. "hundred weight" in cell b2 and "CWT" in cell a2) I would like to have the drop down list displaying the long description in column B to pick from but return the abbreviation from column A. Can not figure it out, what am I missing? SM Tag: How do I get this option avaliable? Tag: 107902
  - 9
    - Macro Issue File Locked for editing Have set up an Icon on Toolbar with a standard Macro which I like to use. But each time I open a new Excel get a message if have multiple instances of excel open.. FILE IN USE. PERSONAL.XLS IS LOCKED FOR EDITING Also How do I respond to a reply to a question placed on this website .... EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com Tag: How do I get this option avaliable? Tag: 107901
  - 10
    - Retrieve text from column Hi. I am wondering if the following is possible and if anyone could assist.... I have data that I am pasting into excel (a1). I cannot adjust the data (using text to columns) as the data may be in different places but always on the same row. Example: All of the following data will be in one column (a1).... Name Joe Blow Sr. Address 1234 MIckey Mouse Lane #4 City Nowhere State Florida Co Name Cold Sizzlers Phone (555)555-4321 I am lookin to retrieve the next 14 characters after the word "name" is found. I am looking ot retrieve the next 12 characters after the word "State" is found. etc.... Ultimately I want to search one column for a keyword and retrieve the x number of characters afterward. Tag: How do I get this option avaliable? Tag: 107897
  - 11
    - Query Howe do I start running a macro? Tag: How do I get this option avaliable? Tag: 107896
  - 12
    - How do I add favorites for Excel 2003 users on a terminal server This is pretty important to my users. In 2000 it was there by default (File>Open on the left hand side the big icons). How do I get the same thing to happen in 2003? There are several files that they only use once a month that have monster long UNC's to access. I'd really like to get this fixed. Tag: How do I get this option avaliable? Tag: 107895
  - 13
    - Adjusting formula so it works even if some rows above empty? I have a dollar amount in cell F23, say, with a formula in G23 to deduct that from subtotal in G22. The formula in G23 looks like this: =IF($F23<>"",($G22-$F23),"") This works great. But I've run into a problem I need to adjust for and can't figure out. These columns, F and G, from rows 18 to 31 are for miscellaneous information. Sometimes I have amounts to deduct, other times it's just info in the description column B to the left of F (there are some merged cells there) about payments in the rest of the spreadsheet and no amount needs go into column 23. So it's just a misc. section of the sheet. But I added an amount in F28 for which the G28 is: =IF($F28<>"",($G27-$F28),""), but which comes out blank since F24 to F27 are empty. So a needed subtraction isn't made. I'm guessing that my formulas in G must accommodate a "range" (do I have the right term?) instead of actual absolute cells? Am I on the right track? What is needed is to have any cell in column F subtracted from the running subtotal of column G even if there are gaps in any of cells in F. Can this be done? TIA. :oD Tag: How do I get this option avaliable? Tag: 107893
  - 14
    - Refreshing data results in "invalid cell reference" I imported some data from a CSV into a worksheet. On another worksheet (named Chapel) I referenced the data in a formula: {=SUM(IF(Chapel!$A$1:Chapel!$A$1000=Categories!$A1, IF(Chapel!$C$1:Chapel!$C$1000=Categories!A$30,1,0),0))} This formula works fine. Now, if I go and refresh my data I get invalid references in my formula: {=SUM(IF(Chapel!$A$1:Chapel!#REF!=Categories!$A1, IF(Chapel!$E$1:Chapel!$E$1000=Categories!A$30,1,0),0))} Is there some way to make this work? And why do some references to Chapel change but others do not? Notice that the first reference to $A$1000 is now invalid while the reference to $A$1 stayed the same, and later references to Chapel changed from "C" to "E". Why is that? Thanks, -- Greg Allen Tag: How do I get this option avaliable? Tag: 107891
  - 15
    - Help with EXCEL please !!!! What I am trying to do is open two spreadsheets in excel but I would like them to open in two different excel applications not in the same application. In other words two different excel windows. Is this possible ? Thanks Tag: How do I get this option avaliable? Tag: 107888
  - 16
    - Lookup? Match? Being driven mad... I have two worksheets. Both have a huge list of employees' names, in a first name cell, then a surname cell. Some of the people appear on both worksheets. I need to produce another sheet that shows every employee that exists on both the original worksheets, plus pulling corresponding employee data from one of the original sheets. I can't for the life of me figure a way to do it. Any ideas? Ben Tag: How do I get this option avaliable? Tag: 107886
  - 17
    - Speech facility Hi, There is a speech facility in Excel which is great for my work but I would like to have it where Excel uses the speech facility on opening a workbook....sort of a welcome and basic instruction as soon as the file is opened. Is this possible?? Jon Tag: How do I get this option avaliable? Tag: 107881
  - 18
    - Complex text import to excel file I want to import a complex text file in xls format. can you help me Text file shows as : VCODE DOCNO VNAME/NARRATION AMOUNT 101101 MAX TECHNOLOGIES 5500151 Purchase -Keyboard 1000.00 5500152 Purchase-Mouse 2000.00 101102 DIGITECH COMPUTER 5500221 Purchase-SMPS 5000.00 5500222 Purchase-Motherboard 10000.00 After importing text file in excel sheet, data should be shown as VCODE VNAME DOCNO NARRATION AMOUNT 101101 MAX TECHNOLOGIES 5500151 Purchase -Keyboard 1000.00 101101 MAX TECHNOLOGIES 5500152 Purchase-Mouse 2000.00 101102 DIGITECH COMPUTER 5500221 Purchase-SMPS 5000.00 101102 DIGITECH COMPUTER 5500222 Purchase-Motherboard 10000.00 You all are requested to please give me guide through VBA macros. Thanks very much kps.rajput email-rajputkps@gmail.com Tag: How do I get this option avaliable? Tag: 107877
  - 19
    - Solver For the Solver, is there any way to determine how many times the target cell has been evaluated, either the total number until the solution has converged or the number for each iteration? (Note that this would not be the same as the number of iterations, since there would likely be several target cell evaluations for each iteration.) I want to use this to determine the relative efficiency of the various search methods and other options. Excel 97, W2K or XP Thanks, Don Culp Tag: How do I get this option avaliable? Tag: 107875
  - 20
    - Modification of a recorded Macro so new sheets take on cell name? Hi All, I have this macro that I've recorded, basically it copies two sheets of a workbook, then pastes them into new work books, then mucks around with some margins and sizes and prints a copy of each. this is working fine at the moment, however I need it to name each of the sheets after a particular cell from the original workbook. Is there a way that I could whack that into the code i've got from my recorded macro? cheers Sub Macro1() ' ' Macro1 Macro ' Macro recorded 25/10/2006 by Scott ' ' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Quote").Select Application.Run "'Quotinator -beta 1.xls'!Sheet2.HideRows" Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Workbooks.Add Cells.Select ActiveSheet.Paste Range("M9:V130").Select Application.CutCopyMode = False Selection.ClearContents ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollColumn = 6 ActiveWindow.LargeScroll ToRight:=-1 Range("A1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.15748031496063) .RightMargin = Application.InchesToPoints(0.15748031496063) .TopMargin = Application.InchesToPoints(0.393700787401575) .BottomMargin = Application.InchesToPoints(0.393700787401575) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 95 .PrintErrors = xlPrintErrorsDisplayed End With Range("B1:K219").Select ActiveSheet.PageSetup.PrintArea = "$B$1:$K$219" Windows("Quotinator -beta 1.xls").Activate Sheets("Public Liability").Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Workbooks.Add Cells.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("L13:AA66").Select Application.CutCopyMode = False Selection.ClearContents ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("B1:K62").Select ActiveSheet.PageSetup.PrintArea = "$B$1:$K$62" With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$B$1:$K$62" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.15748031496063) .RightMargin = Application.InchesToPoints(0.15748031496063) .TopMargin = Application.InchesToPoints(0.393700787401575) .BottomMargin = Application.InchesToPoints(0.393700787401575) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 95 .PrintErrors = xlPrintErrorsDisplayed End With Windows("Book2").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Windows("Book1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Tag: How do I get this option avaliable? Tag: 107874
  - 21
    - sorting or is this consolidating I have an excel file that looks like this: ID# account status 10 1 open 20 1 open 30 1 closed 30 2 transition 30 3 closed I need it to look like this: 10 1 open 20 1 open 30 1 closed 2 transition 3 closed so that all of the rows in the current file are moved to a single row for each ID#. I have no idea how to do this except for manually doing a copy/paste special transpose. For thousands of ID#s - this won't work. Can somebody please walk me thru a way to do this? I don't know how to do macros so I'd need a detailed walk thru. Thanks so much. Tag: How do I get this option avaliable? Tag: 107871
  - 22
    - Protecting Sheets I have a workbook which has about 120 worksheets. Is there any way to set the Worksheet Protection on all sheets at one time? I want all sheets set to allow only the selection of unlocked cells. Tag: How do I get this option avaliable? Tag: 107870
  - 23
    - Opening New Excel file closes the Existing One Automatically Hi! I've got a few templates that we use in our office. We'd frequently create standard workbooks from these templates: We had a shortcut insid the task pane which we click to cerate a new one from the standard template, fill it with data. At the point JUST BEFORE IT'S BEING SAVED (or have a filename), if we open another existing workbook (created from the same template and saved on disk), it would open and Excel would closes the UNSAVED copy without even prompting us to save. This is costing us a lot of time but looking inside the macros of the template, I've not even touch any CanClose sort of methods which plays with prompt. Hope somebody can enlighten us. Cheers! ________ Clifford Tag: How do I get this option avaliable? Tag: 107869
  - 24
    - Migrating Macros to new log-on We've just been migrated to new servers, and with them came a new log-in. I had a whole bunch of macros under my old log-in, and if I go back to it, they're still there. How can I move them to the new log-in? If I just have to re-key everything I can do that, but I hope there's an easier way. I'm using Excel 2000 on an XPSP2 system. Thanks in advance. Tag: How do I get this option avaliable? Tag: 107861
  - 25
    - VBA help needed I have a sheet in which i can insert a new row with data through a user form. After I fill in the user form I get a new row with the data from the user form. Now I would like to insert two extra empty rows (witch I can fill in myself) with the user form action with in the second cel of the extra rows a text like A in the first extra row and B in the second extra row. Is that possible?? Under is a part of the code where the action in is (I think). If wanted I can post the complete code of this action. Sub autotiming() Application.ScreenUpdating = False Dim Msg, Style, Title, Response Dim tk As Variant Dim ph As Variant If Range("keyflag").Value = 0 Then protection.Unprotectit If Range("startdate").Value < Now - Time Then Title = "Start Date occurs in the past" Style = vbYesNo Msg = "The project start date '" & Range("startdate").Text & "' specified in the Resources sheet occurs in the past." & Chr(10) & Chr(10) Msg = Msg & "Would you like to update it to todays date?" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then Range("startdate").Value = Now - Time End If End If Application.StatusBar = "Clearing previous auto-timing" Call clear_autotiming Application.ScreenUpdating = False protection.Unprotectit Application.StatusBar = "Running Auto-timing..." For Each tk In Range(Range("start_task").Offset(1, 0), Range("ins_task").Offset(-1, 0)) If tk.Offset(0, 13).Text <> "" And tk.Offset(0, 17).Text = "" Then tk.Offset(0, 14).Value = finishdate(tk.Offset(0, 13).Value, tk.Offset(0, 10).Text, tk.Offset(0, 11).Value, tk.Offset(0, 12).Value, tk.Row) End If Next For Each ph In Range("phases") For Each tk In Range(Range("start_task").Offset(1, 0), Range("ins_task").Offset(-1, 0)) If tk.Offset(0, 1).Text = ph And tk.Offset(0, 3).Text = "" And tk.Offset(0, 17).Text = "" And tk.Offset(0, 25).Text = "" Then tk.Offset(0, 13).Value = startdate(tk.Row) tk.Offset(0, 14).Value = finishdate(tk.Offset(0, 13).Value, tk.Offset(0, 10).Text, tk.Offset(0, 11).Value, tk.Offset(0, 12).Value, tk.Row) End If Next For Each tk In Range(Range("start_task").Offset(1, 0), Range("ins_task").Offset(-1, 0)) If tk.Offset(0, 1).Text = ph And tk.Offset(0, 3).Text <> "" And tk.Offset(0, 17).Text = "" And tk.Offset(0, 25).Text = "" Then tk.Offset(0, 13).Value = startdate(tk.Row) tk.Offset(0, 14).Value = finishdate(tk.Offset(0, 13).Value, tk.Offset(0, 10).Text, tk.Offset(0, 11).Value, tk.Offset(0, 12).Value, tk.Row) End If Next Next For Each tk In Range(Range("start_task").Offset(1, 0), Range("ins_task").Offset(-1, 0)) If tk.Offset(0, 13).Text <> "" And tk.Offset(0, 17).Text <> "" Then tk.Offset(0, 27).Formula = tk.Offset(0, 13).Value tk.Offset(0, 28).Formula = tk.Offset(0, 12).Value tk.Offset(0, 29).Formula = tk.Offset(0, 17).Value tk.Offset(0, 30).Formula = tk.Offset(0, 12).Value End If Next Range("O5").Select Application.StatusBar = "Ready" Application.ScreenUpdating = False protection.Protectit Else Style = vbCritical Title = "Trial Period Expired" Msg = "The trial period for this application has expired." & Chr(10) & Chr(10) Msg = Msg & "This program cannot be executed until a valid registration key is entered on opening this workbook." Response = MsgBox(Msg, Style, Title) End If Application.ScreenUpdating = True End Sub -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ Tag: How do I get this option avaliable? Tag: 107858

I'm trying to insert check boxes on the excel spreadsheet. We have th
entire page laid out, and now we're trying to insert the check boxes
but there is one option unavaliable. When I click format and go t
properties, Move and size with cells is not avaliable, is there anywa
to make it avaliable?

I'm unsure if its possible. Please, if someone can reply asap it'd be
big help!

Heathe

--
FloridaHeathe
-----------------------------------------------------------------------
FloridaHeather's Profile: http://www.officehelp.in/member.php?userid=502
View this thread: http://www.officehelp.in/showthread.php?t=125632

Posted from - http://www.officehelp.i

Top

Re: How do I get this option avaliable? by Gord

Gord
Thu Nov 09 10:10:00 CST 2006

Use a text box from the Control Toolbox rather than the Forms toolbar.

Gord Dibben MS Excel MVP

On Thu, 9 Nov 2006 19:11:52 +0530, FloridaHeather
<FloridaHeather.2h0dax@NoSpamPleaze.com> wrote:

>
>I'm trying to insert check boxes on the excel spreadsheet. We have the
>entire page laid out, and now we're trying to insert the check boxes,
>but there is one option unavaliable. When I click format and go to
>properties, Move and size with cells is not avaliable, is there anyway
>to make it avaliable?
>
>I'm unsure if its possible. Please, if someone can reply asap it'd be a
>big help!
>
>Heather

Top