Populate cells when Month is chosen in combobox for a specific name

[SChapman]

Hi all,

I wondered whether someone can point me in the right direction with this. I have a combobox that has a list of months, when a month is chosen in the combobox I require a formula to populate the values in each column for a specific name and the month chosen from a data sheet.

I believe INDEX & MATCH will be able to do this however I cannot figure out how to link it with the combobox or how to populate all columns.

Can anyone at least give me some pointers on this? A sample has been provided.


Thanks guys!

[:) Sixthsense :)]

Please attach the file in this forum itself

[SChapman]

I can't as it's too large

[:) Sixthsense :)]

Add it in zip and attach it since you can attach upto 10 MB when the file is in zipped content, I believe.

[SChapman]

Oh I didn't realise that, I've edited my first post to contain the zip file

[:) Sixthsense :)]

Please confirm how you would like to get the results? using formula or using code?

[SChapman]

I would like to know how to do both if possible? If you don't have the time then formula please.

Thanks Sixthsense

[:) Sixthsense :)]

Instead of using combobox to select the Months, use Validation List (Data>>Validation>>List).

In B11 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

Copy B11 cell formula and paste it in C11 and D11 cells.

In B27 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

Copy B27 cell and paste it in C27 to K27 cells.

[SChapman]

Unfortunately I cannot use a validation list due to the font size :/

[SChapman]

Your formula works great, I've only just had chance to look at it. Thanks! How can I use this with a combobox or is it even possible?

[:) Sixthsense :)]

In design mode do right click on the combobox and select PROPERTIES and in LINKED CELL provide output cell reference and locate that output cell in formula.

Sent from mobile device so unable to show it in excel file

[SChapman]

Thanks for that Sixthsense. I've just had a thought, formulas may not work for what I require as I need the user to be able to enter the current months data then I am using a macro to copy it over the the data sheet. Obviously the current solution will prevent the user from being able to input any data into the cells.

Does this mean it would have to be done using VB? :/

[SChapman]

Can anyone help with this?

[SChapman]

bump (sorry)

[:) Sixthsense :)]

Please show your file with combobox linked to a cell to proceed further

[SChapman]

I've attached with the combobox & cells linked in.....

Thanks again Sixthsense

[:) Sixthsense :)]

Please check the attached file for solution

In Module1 code just reduce the number of lines 100000 to your data range for better and speedy performance.

[SChapman]

Thanks for taking the time to look at my problem Is it possible to have the data change automatically when a different month/year is chosen rather than using a macro?

Can I just use the code under a worksheet_change sub instead?

[:) Sixthsense :)]

I just changed the Form Combobox to ActiveX Combobox. Check the attachment for details.

[SChapman]

Excellent, thanks a lot!!!

[SChapman]

Hi Sixthsense,

I am still having problems with this, I've extended your code so it populates the data in rows 11 to 22 & rows 27 to 38. Unfortunately when I choose February as the month & change it back to March only some of the rows populate the data. Is it possible you could take a look at the attached sheet?

[SChapman]

Can anyone help with a solution for this?

[SChapman]

I have now solved this, part of the formula I stupidly overlooked.

Thanks again for all your help!!

[SChapman]

Hi again Sixthsense,

As discussed I've had to add a column & two rows, I came across a problem once I had it all working & that is if a name had changed from one month to the next it wouldn't find them as it is matching the actual name. So I thought it would be best to match a number rather than a name so the names can be variable from month to month.

So, I've managed to get columns 2 to 5, row 11 to 24 working or at least they appear to be but I can't seem to get the correct data to appear in columns 2 to 11, rows 30 to 43.

Do you mind having another look at the attached sample worksheet?

[:) Sixthsense :)]

@ SChapman,

Please try to understand us... it's really hectic one when you change the questions and ask to do changes with the suggested code/formula.

Please plan everything while posting your initial thread itself and show the exact data by covering all your expectation instead of saying on Oh God It's Not Working With This Permutation after that Its Missing This Logic, I expected that it will run...

Our suggestions vary based on the data you show to us and the criteria you supply to us.

Now it's 24th post you are requesting for the change.... we do help but requesting to understand our situation too....

[SChapman]

I do appreciate that and apologise but I didn't foresee this problem. In future I will try and prevent this happening :/

I also appreciate your time and help with this.

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[SChapman]

I have changed it slightly & it appears to work now....

Please Login or Register  to view this content.

I will add more data & retest later today.

Thanks Sixthsense!