Macro to triple filter a data in excel

[marvinlajiant]

Hi
just wandering if this function is basically doable via macro in excel, using a user form which does triple filtering to show the result on a text box


First filter menu: to filter the month (Row 3 on the attachment)
Second filter menu: to filter the cost centre (column B on the attachment)
Third filter menu: to filter the account description (column C on the attachment)

then the result is shown on a text box which is editable, and the result edited would then change the value of the cell previously filtered

for example:
if i choose:

SEPTEMBER, REGION 2, PURCHASES
the result shown on the textbox is 8 (F8)

if i edit the purchases amount to 10, then F8 is also edited to 10




thanks in advance!
Marvin

[arlu1201]

You can try and put all the headers in one row so it will be easy to filter. Also, what is the purpose of the text box? Once you have the filtered results, you can automatically change it in place.

[marvinlajiant]

Hi
the matter i am querying is basically linked with another set of data for budgeting purposes

our spreadsheet's budget data consists of 3 items:

1. average of last year's similar transactions
times
2. predicted movement of the market based on percentage (i.e. next month is expected to be 105% of last year's same month)
plus or minus
3. the expected increase or decrease of the some transactions

point 1 and 2 isnt a problem, however point 3 is another issue as we have huge set of data and we think it is rather time consuming to add the incremental increase/decrease manually on the data itself.

we'd like to have a userform which shows a field for an account for a particular month for a particular cost centre, and for the user to enter either an increase or decrease expectation.

is this idea a bit unrealistic?

[marvinlajiant]

Bump!
need help on this bad

[kvsrinivasamurthy]

Pl see the attached file. K5 cell contains the required formula.Any clarifications welcome.Pl convey your views.
Regards

[kvsrinivasamurthy]

Hello,
With the help of extra sheet "wkg" I have tried to solve your problem. In sheet "wkg" you drag formulas in columns A to K downwords as much as you require.Selection of month,Cost cenre and Account description are given in cells M1,M2 and M3 in Sheet1.Result is displayed in N2 cell. If you want you can hide "wkg" sheet.No Formulas are used in Sheet1.Excel file attached.

With regards.

[marvinlajiant]

Thanks for the reply, K

however the higher up really loves fancy stuffs and they asked me if i could do a combobox for them,

i have attached a sample of the combobox needed.

Months: First Drop down menu which lists all months on the spreadsheet (D2 to I2)
Cost Centre: 2nd Drop down menu which lists all cost centre,without duplicates duplicates (Column B)
Account: 3rd drop down menu which lists all accounts, without duplicates (Column C)

an editable text box below which shows the value of the parameters chosen above (3x parameters)

i.e. $6 on the example For JULY, REGION 1, SUNDRIES (cell D6)


also if the user thinks that this value is incorrect, and the user edits the textbox,the value in the cell becomes the new edited value

thanks

Marvin

[jaslake]

Hi marvinlajiant

Try the attached. Let me know of issues.

[kvsrinivasamurthy]

Hi marvinlajiant
Pl see the attached file. Pl convey your views.

[marvinlajiant]

Works like a charm! thanks to you two!

[jaslake]

Hi marvinlajiant

You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".
To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

[marvinlajiant]

Hi John

Thanks for the codes that you sent me, it was amazing and it exactly do what i wanted previously

I have marked the thread as being ‘SOLVED’ as the spreadsheet you attached has filled my general purpose of looking on how the codes should look like

I tried to implement the codes in my actual larger spreadsheet, however i found no luck as it kept on returning error message

I am fairly new to VBA, so i do not have full knowledge on how to connect/point spreadsheets etc, so here are few quick questions

My questions are:

1. If the data is located on another worksheet in the same spreadsheet, which part of the code do i need to change ?
2. Does it matter if the data is way larger than the sample which i showed you?
3. The placement of the data on the diff sheet itself is different from the sample which i sent you (i.e. different ROWS...columns are the same), do i just need to change the ranges (ROW,COLUMN) or is there any other thing that i need to be aware of?

Please let me know if you could, and that would be greatly appreciated

Regards

Marvin

p.s. i tried to send you pm but don't think it worked for some reason..

[jaslake]

Hi Marvin

Regarding this

If the data is located on another worksheet in the same spreadsheet, which part of the code do i need to change ?

The code focuses on a worksheet named "Sheet1". If your worksheet name is different you can use VBA to do a mass change. Enter VBA (Alt-->F11) Select Edit-->Replace. You'll get a screen that looks like this Find_Replace.jpg

Enter the data as shown, enter your worksheet name in the Replace With box (including quotes)...make sure Current Project option button is selected. You should get 4 replacements. Can't guarantee this'll fix all but it should.

Regarding this

Does it matter if the data is way larger than the sample which i showed you

No.

Regarding this

The placement of the data on the diff sheet itself is different from the sample which i sent you

If the COLUMNS are the same then the COLUMNS are OK. However, if your Header Rows are different (Rows 1 and 2 in the sample file) and your data starts in a different Row (Row 4 in the sample file), then there's trouble in River City. If this IS the case attach a file that truly represents your actual data. I'll fix it (or you can).

[marvinlajiant]

Hi John

tried to fix it on my own and i think my excel crashed on me

attached is the file sample which looks identical to the original spreadsheet that i have (eliminating tabs with other functions which do not connect to these 3 sheets), unfortunately the size of my orig sheet is to big for me to attach so i only took the 3 main tabs of the spreadsheet

P&L Summary : where the button should be
ABud: Budget Sheet for the first company
PBud: Budget Sheet for the second company

function needed is still the same:
button pressed
userform is shown
combobox 1 shows month : Column V5 to AG5 on the Bud spreadsheets
combobox 2 shows Cost Centres (Column T both bud spreadsheets)
combobox 3 shows account description (column U both bud spreadsheets)
textbox shows the value after the criterias selected as above comboboxes
if textbox is changed, value of the cell is also changed

p.s. fields needed to be indexed/changed highlighted in Cream. the similar fields to the left (column A to R) are needed for other purposes


thanks heaps for this.. i know i should've read more of the VBA for dummies book i bought half a year ago..

Marvin

[jaslake]

Hi Marvin

Unfortunately the original sample file you posted bares no resemblance to the new file you posted. Also, the new file you posted contains no code and no data so I can't begin to figure out what you've trying to do.

You indicate that

unfortunately the size of my orig sheet is to big for me to attach

Try zipping the file. Before you do so include some data, include code you've tried AND INDICATE what you wish the results to be.

In addition...please mark this Thread as UNSOLVED. If you can't do so yourself PM any Moderator and ask them to do so.

PS: I'm color blind...you'll need to tell me what this is

fields needed to be indexed/changed highlighted in Cream

[marvinlajiant]

Hi John

apologies, i was referring to the filter format (i.e. months, area and account desc). i could not attach the zipped files somehow? any other way of getting it to you?

the file itself does not have much data, as they are still yet to be entered by our accounts department. the only data available are on "Pbud" and "ABud" tabs and they are rough numbers.

the codes which you provided (which i tried to replicate) are on Userform4 and Module 7, activated by pressing button2 located on the Summary tab. this is currently only available for Pbud (Abud to follow).

data in column V to AG in the Pbud sheet is intended to be blank at the start. data in column V and AG is intended to be an additional "adjusting figures" for the whole budget figure. for example:

Budget for Administration, General Expenses, July is calculated as $1111.889 (tab Pbud D313). if the budgeting department thinks that this is insufficient and would like to add $3000 on it, then they will need to enter the $3000 in cell V313 on the same spreadsheet, rather than changing the value of D313 (as D313 is formulated). the budgeting dpmt can always edit column V-AG manually, but we'd like to speed this process up by introducing the userform.

in summary, what we really want happening:

- button 2 on summary tab is clicked
- userform is shown
- user selects the criteria (month, region, acc code)
- value of the cell of the selections above displayed (which default is blank)
- if user enters the value on the textbox and presses update, value of the respective cell is also changed.


fields needed to be indexed/changed highlighted in Cream: refers to column S to AG on both "Pbud" and "ABud"


hope this makes sense.. im in accounting team ... which probably explains why it is hard for me to explain things!

regards

marv
[email protected] is my personal email if you would like to send me a handshake email

again thanks for your patience...

[marvinlajiant]

still no luck with the file uploading... not sure why

limit is 9 mb and my zip file is only 1.4mb...

[jaslake]

Hi Marvin

See PM

[jaslake]

Hi Marv…I think I’m slowly beginning to understand. The changes, if any, will go INTO columns V to AG and cells will be highlighted.
I’ll pick a color for Cream…it may well not be Cream to your eyes…but you can change it.

OK…if my understanding is correct…should PBud and ABud have the same structure? Which one is correct?
The structure question is critical because the code is written to the structure of the worksheet.

When the button is pressed on P&L Summary do you want to stay on the P&L Summary screen or do you want to go to PBud and/or ABud screen?
This question is also critical as it changes the way the UserForm4 is populated.

I’m at a bit of a standstill until I hear from you.