Posts by martinfrench

    Re: Cell Date In Formula Reference

    Many thanks for the reply Ger. It is a summary that will be added to each week so having all previous week's sheets open at the same time wont work. Mainly as its the clients team who will be adding their own details weekly to create a weekly comparison table.

    Will have to keep trying other things

    Thanks again

    M[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Does anyone know if this is possible using UDFs ??


    Using cell contents in formula to Populating cells with values from closed workbooks

    I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row.

    I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A.

    I have attached a sample spreadsheet to try and make it clearer. Any help would be gratefully accepted.



    Re: Adding Contents Of Same Cell Across Numerous Worksheets

    Hi Richard

    Thanks for the speedy reply.

    My worksheets are all separated by a daily summary i.e.
    monday, mondaysumm, tuesday, tuesdaysumm....... etc

    Can I specify the individual daily worksheets in the formula or do I need to put them next to each other in the workbook?



    Hi all

    I have a workbook with 5 worksheets - one for each working day, and another worksheet to summarize all the daily figures into a weekly report.

    If I want to add all the cells b3 from each of the daily worksheets I am currently starting with = in the cell where the result will appear, then going to each cell B3 in each of the daily sheets putting + in each time.

    Is there a quicker way to type a formula which will add all 5 daily figures together?



    Re: Using Dynamic Ranges in Charts

    Hi Shak

    In response to your other question regarding the formating of the cell contents.

    Reformat the whole sheet to show the text in white. This will effectively make all text invisible. Then do a conditional formatting to change the text colour to black of all cells where the contents are greater than zero. All the numbers will re-appear but the #N/A will stay white so remaining invisible.

    Hope this helps


    Re: Relative addressing in a UDF

    Excellent. It works. Thanks. The only problem I have now is that everything is fine when I copy the range. However, if I add or remove from say columns Z or AA, which would alter the carry forward balance, the udf in the following month, cell AH3 does not automatically update itself. I can copy the formula back to AH3, but is there a way to do this automatically/



    Hi everyone
    I am trying to create a home accounts system with credits, deposits and balances etc
    I want to use a User Defined Function (udf) which picks up the value at the bottom of a list 6 columns to the left. I want to be able to copy the udf to a cell 6 columns further to the right and for the addressing to be relative.

    I have the formula for picking the value at the bottom of a list but I am struggling with getting the udf to automatically select the column 6 columns to the left. I have attached the spreadsheet. I would be grateful for any help on this. Thanks

    Hi Everyone,
    I am writing a procedure which needs to capture the first and final week numbers of a range to be looked at by an analyst. ie the range week number 3 to 6. I have set up a userform with two comboboxes which are populated within the code with week numbers 1 to 52. I want to be able to pick a week number from each box and pass those numbers back to the main macro so it can load the appropriate range of files for processing. I have put msgboxes in the userform coding to check the week numbers are being picked up correctly, and they are. But when the userform is closed down (.hide) the main running macro does not know what week numbers were selected. Is there an easy way to make sure the values pass over?

    This is the code in the main macro:

    Sub Macro1()'
    Dim firstweek As String
    Dim Secondweek As String
    MsgBox "(Main Macro)First Week = " & firstweek
    MsgBox "(Main Macro)Second Week = " & Secondweek
    End Sub

    This is the code in the Userform:

    Private Sub CommandButton1_Click()
    'Selected the Continue button
    firstweek = ComboBox1.Value
    Secondweek = ComboBox2.Value
    MsgBox " first and second = " & firstweek & ", " & Secondweek
    End Sub

    Private Sub CommandButton2_Click()
    'Selected the End button
    MsgBox "End selected "
    End Sub

    Private Sub UserForm_Activate()
    ComboBox1.List = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", _
    "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", _
    "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", _
    "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", _
    "47", "48", "49", "50", "51", "52")

    ComboBox2.List = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", _
    "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", _
    "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", _
    "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", _
    "47", "48", "49", "50", "51", "52")
    End Sub

    Any suggestions will be gratefully accepted

    Martin French

    Hi Everyone. I have a list account numbers in one column and the balance for each account in the next column. I need to use vba to calculate the sum of the balances for the all the accounts in the list for use later in the procedure, but do not want the total to appear on the spreadsheet. I'm sure it must be simple but I am stumped. TIA for any replies. Martin French UK

    Hi again
    I have simplified the code for the purpose of demonstrating this unusual behaviour.
    The line of code in the main macro which calls the secondary macro is: "2ndplacedatabase_sept_2003.xls!dummymacro()"
    MsgBox "now back in main program"

    The code in the secondary sheet is:

    Sub dummymacro()
    MsgBox "Now in the dummy macro"
    End Sub

    When I run this simple code the message box in the secondary macro appears twice before control passes back to the main macro.


    Hi Everyone. This one really has me stumped but I'm sure the answer is dead simple. I have written a macro in one spreadsheet which then calls a macro from another. The control then passes back to the original macro. It was created in XL97 under windows98SE. My machine has now been upgraded to XP and XL2002. When I run the macro now, it runs the called procedure twice before returning to the original coding. Does anyone know why this happens and is there a way to stop it? Many thanks to anyone who can help.
    Martin French (UK)

    Hi everyone. I have two spreadsheets. One with "new data" on and one is a database of all past data. I have a long macro which runs from the first "new data "sheet. There are a number of occasions where the macro in the database sheet needs to run to check for duplicate information. I can do this by using "Application.Run......". The problem is I need to return to the original code and continue the processing. I need it to act like a gosub but cannot work out how to return the control. In the short term it is necessary for each of the sections of code to stay where they are. All suggestions will be gratefully accepted. Many Thanks. Martin French :confused:

    Hi Roy
    Thanks v.much for taking the time to reply. The main reason for using Word is the preference of people working in that part of the business, and our need to protect the template side of the form. I know in Excel you can unprotect the cells you want people to input information in to, but can you set up the form in excel to automatically toggle between the cells where data is required, as you can in a word form. I would like to ensure that the data input people are forced to visit every cell that we want an entry for. Hope this doesn't sound too fussy but we like to make things as idiot proof as possible!. Many thanks.

    Hi everyone. Not sure if this is the right place for my question, but I hope someone will tell me if there is a more appropriate site.
    I have created a form in Word. I have created the text fields I need. The first one has the current date automatically placed in it. I need anothe field to automatically calculate and display a review date which is 10 days further on. I've tried a few things but cannot get it to work. Any help is much appreciated. Thanks