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 ??

Thanks

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.

Thanks

Martin

Re: Adding Contents Of Same Cell Across Numerous Worksheets

Many thanks

This works brilliantly!

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?

Thanks

Martin

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?

Thanks

Martin

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

M

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/

Thanks

Martin

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

Many thanks for that. It works fine now. I knew it would be something simple but I couldn't find it.
Thanks again

MF

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
UserForm1.Show
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
UserForm1.Hide
MsgBox " first and second = " & firstweek & ", " & Secondweek
End Sub

Private Sub CommandButton2_Click()
'Selected the End button
MsgBox "End selected "
UserForm1.Hide
End
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
Bradford
UK

Thanks Dave. What a quick reply.It works perfectly. Many thanks. Once again I am totally impressed by the quality and speed of the answer.I guess you must never sleep down under. Thanks again

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
I also have this problem and would be grateful for an answer, but what is the XL - G list? How do I view it?
Thanks. Martin

IT WORKS !!
I knew it would be simple, but I couldn't work it out - been a problem for a few weeks now. Many thanks Andy. Hope you can find out why it works - if you do let me know.
Thanks
Martin

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:

application.run "2ndplacedatabase_sept_2003.xls!dummymacro()"
MsgBox "now back in main program"
End

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.

Thanks

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 for the reply - I knew about the protecting bit but not about tab moving from cell to cell. It works fine now so they are going to get it in excel !!!
Thanks again,
Martin

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.
Martin

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
Martin(UK)