Excel >>please reply

please reply

Index ‹ Excel ‹ Excel Misc

Previous
- 1
  - Excel Misc >> Large Pivot Refresh problem Excel 2003I have a large spreadsheet which contains a large pivot
    table (183R * 19C) -based on a largish data set (2730R *
    11C).
    I am unable to refresh this pivot table in Excel 2003 - I
    get error message "There are too many records to complete
    this operation." The spreadsheet refreshes fine under
    Excel 2002 - why not on 2003?
    
    Is this a known problem / is there a workaround (I cannot
    reduce the size of the table).
    
    Jon.
- 2
  - Excel Misc >> List folder names within a directoryHello Gurus and newsgroup users.
    Your kind assistance please.
    
    For some time now I have been using Chip Pearsons code to
    return a list of file names within a specified location. I
    would like to now return a list of folder names.
    
    I cannot find any objects releated to folders.
    
    Is it possible?
    
    Any help would be appreciated.
    
    Kind Regards
    Paul
- 3
  - Excel Programming >> Date Transfer to userformHi,
    On a User From I have a text box where the user enters a date. Two queries
    that I have not yet been able to sort out / find an answer - I would be
    grateful if someone could assist me.
    
    1. On a user form text box - Is it possible to restrict the user to a
    particular date format? i.e. 27/05/05
    
    2. On a userform, Once a user enters the date of their order/activity, how
    do I have that date transferred to 4 text boxes to display in 4 textnoxes
    the day of week, month, year and date. i.e. user enters 15/05/05 and the
    text boxes are populated automatically with Monday - 15 - May - 2005
    
    Any help would be fantastic,
    Thanks,
    Mickey
- 4
  - Excel Programming >> Rerieve Images from xls files and save themI have many files contain image in each sheet, (there are 3 sheets each
    file) and I want to retrieve the images and save them to jgp files? how
    can i do that? please my friend.. i need it immediatelly. due to the due
    date..
    
    Thank's
    
    --
    blackpassword
    ------------------------------------------------------------------------
    blackpassword's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29357
    View this thread: http://www.excelforum.com/showthread.php?threadid=490657
- 5
  - Excel Programming >> Createobject for FTPI am trying to send files generated in an excel application to a FTP site
    I know how to create an InternetExplorer.application object
    Is there documentation on the parameters needed to create a FTP object
    (user,password, input directory, output directory...)
    
    Thanks
    
    AG
- 6
  - worksheet functions >> Linking dates with a calendar...My hope is this...
    
    column A are names of those I have scheduled meetings with.
    column B is a date I enter with the next several columns auto filling using
    a function.
    
    I would like to link this to a worksheet with a calendar so it displays the
    persons name on the calendar on the dates that the worksheet has listed (5 or
    6 each person). Thank you for your help.
- 7
  - worksheet functions >> Matching data to return valueGood Evening and thanks in ADVANCE for looking at my
    problem. I am looking for some of the Forum experts to
    give me a hand please send me or reply with any
    code/equation/idea that makes sense;
    
    I have a workbook that contains 6 worksheets.
    
    1=RegQList
    2=289
    3=Fisher
    4=Grove
    5=Sprauge
    6=Mooney
    (Exactly as typed)
    
    I have in the RegQList hundreds and hundreds ,I think
    2700 specific and unique Regulators that I need, no must
    calculate, various equations for. What I have figured
    out is that if I was smart enough to create a LOOKUP or
    smart eqations to find
    MAKE- Cell J2/MODEL-Cell K2/SIZE-Cell L2/ORIFACE-Cell M2
    
    on each line in the RegQList then if you could help me
    create the code to look at the 5 other worksheets find
    the matching MAKE-Cell J2, then within that 1 of 5
    sheets match-up MODEL/SIZE/ORIFACE (which on the 5 sheets
    is always column A-MAKE/B-MODEL/C-SIZE/D-ORIFACE when
    that match is located/solved I want the number known as
    Cg which is already in COLUMN E of the same Cell rows as
    the matching specific make regulator.
    
    I would like that number returned, or so I wish to place
    this lookup equation to place the Cg for each specific
    Regulator in AA2 of RegQList. Then with that Cg factor I
    can return to my basic Excel skills and create the needed
    equation of (1.29) X ( SUM H2+14.7) X (SUM AA2) this
    equals Q or Flow. The 1.29 and 14.7 are constant numbers
    to calculate Flow.
    
    I am going to play with this all night and morning. SO
    if you think that you can help me figure this out I would
    appreciate it so much I don't think you can even begin to
    understand.
- 8
  - worksheet functions >> Search column for value and return TRUE or FALSEHi!
    
    I function that would search through column of text values (range with 1
    column) and return TRUE if column contains value given as criteria or FALSE
    if there is no such value in given column
    
    For example i have range A1:A10 and i want to find out whether this range
    contains value x or - x
- 9
  - Excel Misc >> Need formula Help soonB C E G
    
    2 SubtotalRetail SubtotalCost SubtotalComsn SubtotalGrossProfit
    3 =Formula(j7:j10) =Formula(K7:K10) =Formula(l7:l10) =Forml(m7:m10)
    
    A B C D I J
    K L M
    6 Date PrdoctID QtyOut QtyNow Descpn Retail Cost CmsnGainLoss
    7 26.11.07 200700105 2 8 Stonefgr 1300.00 500.00
    01600.00
    8 26.11.07 200700106 11 0 Stonefgr 3000.00 600.00
    26400.00
    9 26.11.07 200700068 2 22 Textiles 600.00 320.00
    00560.00
    1026.11.07 200700249 1 0 Paintings 21000.00 7000.00
    14000.00
    
    Sir, just same format of my worksheet. I need a formula in B3, C3, E3 and ...
    I want to see what is the exact Gprofit on particular date after data>Filter
    of Date Column
    
    I hope I now cleared to you all thing and you will better understand.
    
    Regards
    
    RRS
- 10
  - Excel Programming >> Formatting dataHi
    
    When formatting the same type of data frequently, I
    design a template with three sheets, the first to paste
    the raw data, the second a temporary for the
    fuctions/formulas for fomatting the data in the former
    sheet and the last sheet is to paste the temporary sheet
    data as values. Please see example below.
    
    In the second sheet I write the formulas for more than
    the rows of expected data. Say somtimes 200 rows as the
    rows of data varies from 10 to 150.
    
    My questions are,
    
    1. Is this the most efficient way of doing this task?
    If not what is a better solution.
    
    2. How can I adapt my function/formula sheet to only
    write fuction/formulas only for the number of rows in the
    data sheet?
    
    Data sheet
    
    ID CustName ProductCode
    12 Joe Bloggs FEN
    
    Temporary sheet
    
    ID ProductName
    12 Fulltime English (This name is derived from the
    ProductCode in the Data Sheet and a lookup table using
    the Vllookup function)
    
    Final sheet
    Just copies the Temporary sheet as values using macros.
    
    Thanks.
- 11
  - Excel Programming >> Save only new or changed cellsIs there a method that would allow me to save ONLY cell
    values that have changed OR a specific set of cells?
    
    AND bring them back in later ...
    
    to do what if analysis.
    
    Don't particularly want to write macros / VBA, but can do
    if that is the methodology.
- 12
  - worksheet functions >> Sheets don't always calculate automaticallyHi all
    
    When I enter data Excel doesn't always calculate automatically - If I hit F9
    when prompted at the bottom of the screen the worksheet updates OK.
    
    There doesn't seem to be a specific pattern - it's not always the same cells
    that do it.
    
    I have calculate automatically checked on the options tab.
    
    Thanks in advance
    
    Andy
- 13
  - Excel Programming >> Combo box background colourI have a combo box and it is linked to a cell a2.
    When I select a value from the combo box drop down list this obviously
    changes the value in cell A2. Is it possible to have the background of the
    como box automatically change colour depending on the value of cell a2?
    e.g. Cell A2 = 1, combo box background = red
    Cell A2 = 2, combo box background = green
    Cell A2 = 3, combo box background = blue
    Thanks for any help
- 14
  - Excel Misc >> Working with dates previous 1900I'm working in ancestry studies and I can not use dates previous to 1900 as
    dates.
    I need to use then in order to sort lines by born dates.
    How cah I do?
    --
    Thank you very much
    Mar
- 15
  - Excel Programming >> UserForm QuestionI have a userform with a text box in it. I want the user to enter the month
    and date in the format mm/yyyy. In the text box, I want to display mm/yyyy,
    but when the user selects the TextBox1, I want it to clear out. What do I
    need to do to accomplish this?
    
    Thanks,
    Barb Reinhardt

Author

Message

PCCKen

Posted: Sun Jan 25 12:24:32 CST 2004

Top

Excel Misc >> please reply hi i have a proble
i want to format a cell such that it can accept only 13 digits before the decimal and 9 digits after the decima
kindly repl
srikanth

Excel229

Ian

Posted: Sun Jan 25 12:24:32 CST 2004

Top

Excel Misc >> please reply Using data validation you can set Allow:decimal, Data:less than and Maximum
10000000000000. This will stop users entering more than 13digits before the
decimal point. I don't know of a way to limit the decimal places entered,
but you can set the cell formatting to only show the first 9 places.

In data validation you can alos set an error alert to let the user know why
their data has been rejected.

Ian

"sri" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> hi i have a problem
> i want to format a cell such that it can accept only 13 digits before the
decimal and 9 digits after the decimal
> kindly reply
> srikanth

Peo

Posted: Sun Jan 25 12:22:52 CST 2004

Top

Excel Misc >> please reply You must use a text string for this since excel has only 15 digits precision

--

Regards,

Peo Sjoblom

"sri" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> hi i have a problem
> i want to format a cell such that it can accept only 13 digits before the
decimal and 9 digits after the decimal
> kindly reply
> srikanth

Index ‹ Excel ‹ Excel Misc

Next
- 1
  - Excel Misc >> Net profitHi
    I have a purchase price in A1 0f 1.12 and a selling price in A2 of 1.95
    how do i get the net profit please
    
    RegRDS
    mIKE
- 2
  - worksheet functions >> degrees formulaon a calculator I would
    divide the smaller number by the larger number and then inv tan to get
    the degrees
    what is the simplest way to get the degrees from a spreadsheet formula,
    I have found one way, by getting the two numbers and using radians and
    soforth and finally getting my degrees after a couple of steps, there
    should be a way to just take the two numbers and formulate degrees
    from them
- 3
  - Excel Programming >> how do i programmatically insert a button on a worksheeti want to programatically insert a command button into a worksheet that is
    not the active worksheet. I dont care about placement, size , just that the
    command button is on worksheet "main" and the command button is "emp1".
    
    thanks
- 4
  - Excel Misc >> double zerosi have a column where users will enter seconds. however, if the value i
    '00' it shows up at '0'. i made a custom format for the column using
    '0#' format, so every other entry works. any ideas?
    
    btw, a later column uses this column for calculations so making it tex
    format will not solve the problem.
    
    thanks,
    jaso
    
    --
    Message posted from http://www.ExcelForum.com
- 5
  - Excel Programming >> Clearing a columnHi all,
    
    This question is in two parts.
    
    1) How do you clear an entire column of date in vb code?
    2)Is it possible to clear an entire column excluding some cells withi
    that colume? For example;
    
    Lets say i want to clear column "A" but want to exclude "A1" and "A2
    from this clear function
    
    Is this possible? How!
    
    Cheers al
    
    --
    Message posted from http://www.ExcelForum.com
- 6
  - Excel Programming >> Application.DecimalSeparatorHi all,
    
    I use Application.DecimalSeparator in a function to strip characters from a string and leave just a number.
    I noticed that, if you have Tools>Options>International, Use system separators checked, this will not be reflected in
    Application.DecimalSeparator.
    Can anybody tell me what I should use to get that?
    
    Thanks,
    --
    Kind regards,
    
    Niek Otten
- 7
  - Excel Programming >> Excel.exeI have a pc running Win NT and office 2000 excel.exe
    sortcut will not work. but if I log on as administrator
    it work. err is exception access voilation 0xc00000005
    address 0x30026E31
- 8
  - worksheet functions >> Using named range to extend print area for variable number of columnsI've used the following method from D. Peterson to automatically set
    the print area to extend to the last row which contains actual data.
    
    Now, I'm faced with a similar situation which the 'columns' are now
    the variable component.
    Rows to be printed begin at A1:A18 and will always include columns A
    and B; there may be as many as 40 columns to include, or as few as the
    1st two
    
    These are the 2 components of the named ranges which will set the
    number of rows(for reference):>>
    
    Insert|Name|Define
    Names in workbook: Sheet1!LastRow
    Use this formula
    Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<>""),ROW(Sheet1!$A$1:$A
    $1000))
    
    (Make that 1000 big enough to extend past the last possible row.)
    
    Then once more:
    Insert|Name|Define
    Names in workbook: Sheet1!Print_Area
    Use this formula
    Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)
    
    That last 3 represents the last column to print (A:C in my example).
    
    And change the worksheet (sheet1) if necessary (in all the places).>>
    
    Any ideas? I'd like to keep the named range concept if possible
    Thanks for any thoughts.
    
    Pierre
- 9
  - Excel Programming >> Hyperlinks on Protected SheetsHi,
    
    I have a protected sheet with some hyperlinks on it. However I am unable to
    click on any of the hyperlinks.
    
    The only protection option with anything to do with hyperlinks is the Allow
    Insert Hyperlinks option, which has no effect.
    
    My goal is to have a protected sheet with formulas hidden, yet the user can
    click on hyperlinks.
    
    BTW the Hyperlinks are inserted as worksheet formulas if that matters...
    
    Thanks
    Richard
- 10
  - worksheet functions >> can i look up and produce a bmp?I want if cell a1 = true, put "graphic.bmp"
    
    is this possible?
- 11
  - Excel Programming >> copy to a text boxis it possible using a command button to copy text from a cell in sheet1 and
    paste it into a text box in sheet 2
    thankyou john
- 12
  - Excel Programming >> Page Protection interfering with Hiding Rowshave a sheet that hides groups of rows based on a cell's information.
    I'd prefer this to happen automatically through VBA, but currently I'm
    stuck using a Toggle Button to hide/unhide these rows.
    
    The problem I run into though is that that toggle button works
    brilliantly until I turn on the page protection. Once I turn on the
    protection I keep getting errors that the page is protected and can't
    be updated.
    
    And due to the fact that the people who will be using this know
    absolutely no Excel at all and break formulas on the old sheet
    regularly... well, the sheet has to be write-protected.
    
    The code I'm currently using is this:
    Sub DENYPASS()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    If Range("B2") = 1 Then
    Rows("11:41").EntireRow.Hidden = True
    Rows("42:72").EntireRow.Hidden = False
    Else
    Rows("11:41").EntireRow.Hidden = False
    Rows("42:72").EntireRow.Hidden = True
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    End Sub
    
    with a private sub to connect it to the Toggle Button
    
    Private Sub passdeny_Click()
    DENYPASS
    End Sub
- 13
  - Excel Programming >> Problems with copying and pasting rangeHi,
    
    I have developed the macro below to take a number from a dataset (in
    "Sheet1"), copy and paste it into a model (in "Sheet2") and then copy an
    paste the model output back into a different column in the original dataset
    (in "Sheet1").
    
    Sub Macro()
    Dim cell As Range
    For Each cell In Worksheets("Sheet1").Range("Range1")
    cell.Resize(1, 1).Copy
    
    With Worksheets("Sheet1")
    .Range("Cell1").PasteSpecial Paste:=xlValues, Transpose:=True
    cell.Offset(0, 9).Value = .Range("Cell2").Value
    End With
    Next
    End Sub
    
    Here is my issue, rather than simply pasting one value from "Cell2" back
    into "Sheet1", I would like to paste a "Range2" back into "Sheet1",
    offsetting this value [0,9] from "Range1". However, when I put in a range
    rather than a cell-reference in "Cell2", the macro stops working. Does anyone
    have any suggestions as to what I am doing wrong?
    
    In advance, thank you very much!
- 14
  - worksheet functions >> Force "+" signI was wondering if there was a way to create a number format that displays the
    plus sign on positive numbers (and the minus sign on negative numbers).
    Thanx,
    -plh
    
    --
    I keep hitting "Esc" -- but I'm still here!
- 15
  - worksheet functions >> A calendar calculator template that reflects remaing weeks in a yeHello! :-)
    
    I am trying to create a calendar calc tool that, when I work on a sheet, it:
    
    1) Automatically loads today's date
    2) Automatically loads number of pay periods remaining in the plan year
    (using a pop down to allow selection of weekly or bi-weekly).
    3) Accepts a deduction amount per pay period.
    4) Calculates a total annual payroll contribution during the plan year
    (based on number of pay periods remaining in the plan year, multiplied by the
    deduction).
    
    For Example:
    1) Our Plan year: 08./01/05 - 7/31/06
    2) Automatically load of today's date: 9/15/05
    3) Drop down prompting choice of weekly or bi-weekly pay period: Weekly
    3) Remaining pay periods between 9/15/05 and 7/31/05: 46
    4) Weekly deduction: $28.00
    5) Estimated Maximum Contribution: $1288.00 ($20 * 46 pay periods).
    
    ----------------------------
    
    Along the same lines...let's say the goal of the employee to make an annual
    maximum of $5000 a year. He wants to know how much he must contribute a pay
    period to reach his goal of $5000 within the plan year. Can I create a small
    spreadsheet tool that will automatically tell the employee how much he/she
    needs to have deducted from his/her paycheck by 7/31/05 in order to reach the
    goal of $5000, beginning with payroll of 9/16/05?
    
    This seems like it should be easy, but I cannot get it to work? If you have
    an Excel spreadsheet you can send as a template, or if you can describe to
    process of creating this, I would be so appreciative!