# Posts by smartphreak

• ## simplification of sumif forumla

Hi all,

I am currently using this formula and was wondering if there is a simpler way of using it.

in cell C9 of the summary sheet:

=SUMIF('019'!A1,A1,'019'!C9)+SUMIF('020'!A1,A1,'020'!C9)+.... this start at 019 and goes through to 075 which is why I need it done a little simpler?

• ## adding the same cell across multiple worksheets based on criteria on the worksheet

Re: adding the same cell across multiple worksheets based on criteria on the workshee

added workbook to try explain what I need doing

• ## adding the same cell across multiple worksheets based on criteria on the worksheet

Re: adding the same cell across multiple worksheets based on criteria on the workshee

no.

the formats of the sheets are identical.
sheet1 has "EPN" in cell A1, sheet2 has "EPH" in cell A1, sheet 3 has "EPN" in cell A1

I want to add cell Q17 on sheets1 to sheet 3 providing that it has "EPN" in cell a1 of the sheets

make sense?

• ## adding the same cell across multiple worksheets based on criteria on the worksheet

Hi All,

I know this should be simple - but my brain has decided to stop functioning, so please help...

I have a bunch of Income Statements with a business unit reference in cell A1, i want to consolidate by having another worksheet that adds up the same cell across the income statements providing that cell A1 in the worksheet = A1 in the consol.

How do you attach a workbook example?

• ## transferring combobox value from one user form to populate combobox on another

Re: transferring combobox value from one user form to populate combobox on another

Hi there - i am now getting a Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction Class.

This is the code that is giving problems now (CheckCode):

Code
``````page = Me.ssComboBox_SelectBranch.Value
Else
Source = page + "DEBTOR"
DebtorCode = Me.ssTextBox_DebtorCode.Value
CheckCode = Application.WorksheetFunction.VLookup(DebtorCode, Range(Source), 2, False)
If IsEmpty(CheckCode) = True Then
Call AltCode
Else``````

I think it has to do with the fact that DebtorCode is not in the range.... any ideas?

• ## transferring combobox value from one user form to populate combobox on another

Re: transferring combobox value from one user form to populate combobox on another

Thanks - I knew it was either super complicated or uber simple!!!!!

above i meant BRANCH not DEBTOR... works a charm!

• ## transferring combobox value from one user form to populate combobox on another

Re: transferring combobox value from one user form to populate combobox on another

Hi Perhaps I was not clear in my issue...

The second form ssForm_ChooseDebtor needs to be called when the error is located during the worksheetfunction.vlookup stage in the ssButton_ProcessReceipt_Click.

When that form loads the user hasd an option of:
1. entering the code again (incase they made a finger error) in ssTextBox_WrongCode or
2. selecting from the drop down list ssComboBox_WrongCode

In situation 1 above the user will input the code and the "select Debtor" button which will then check for a valid code again and return the same box or proceed with the insertion of the data and per the normal working script. Although this has not been coded yet and will be easy enough to (I THINK!!)

Situation 2 above is where I have a bigger problem. I need this user form to know which branch I have selected on ssForm_AddReceipt.ssComboBox_SelectBranch so that it can use that value to lookup and populate the appropriate list of debtor codes on "Lists" sheet. The working example here is REGION 1 and BRANCH 5.

So I need ssForm_ChooseDebtor to have code that takes ssForm_AddReceipt.ssComboBox_SelectBranch.Value (which is "DEBTOR05") calls it a variable and then adds "CODE" to it to make ssForm_ChooseDebtor.ssComboBox_WrongCode.RowSource = variable + "CODE" ("DEBTOR05CODE")

I hope that is more clear. I have a feeling is has to do with declaring Public Variables but I have never needed to do that before so I am a little clueless.... Or it is something simple that I am overlooking.

Hope you can help.

• ## transferring combobox value from one user form to populate combobox on another

Hey all,

I have tried to upload my sample workbook...

I have a working script to run adding payments... it performs a check of the details entered and only runs if the criteria i want are there.

The problem I have is if the "Debtor Code" is not correct I want to open another userform with the option to try enter the code again in a textbox or select a debtor code/name from a combo box.

I thought about running all the code in the module and assigning public variables but that doesnt seem to work as I need the result of a combobox selection to be carried to the next userform...

Any help?