Text formulas across multiple worksheets

[cbell25]

Hi,

I am looking for a little help to create a formula across multiple worksheets.

I have an excel workbook with 9 identical worksheets contained within it. On each worksheet there are drop down menus in cells E15, E16 and E17 - for the purposes of this thread lets say the options on the drop down menu are Option A, Option B, Option C and Option D.

There is also one further worksheet (the master sheet) that I would like all of this information to pull through into. Essentially what I would like is in cells E15, E16 and E17 to have a formula that would give the top 3 options from across the 9 sheets. So for example if the most commonly chosen option is Option A that would show in cell E15 on the Master Sheet, if the second most commonly chosen option was Option D this would show in cell E16 on the Master Sheet and so on.

If I'm honest I have no idea whether this is possible or not but any assistance would be greatly appreciated.

Many thanks in advance.

[Tony Valko]

Using just the built-in worksheet functions this is not possible.

If the data was all on a single sheet then it can be done.

Are you willing/able to put the data all on a single sheet?

[ARGK]

Hi
You can use this formula on the Master Sheet to count multiple instances of chosen options across multiple sheets (you will need to modify the cell references and add the number of COUNTIFs as you need:
=COUNTIF(Sheet2!A1:E1,"Option 1")+COUNTIF(Sheet3!A1:E1,"Option 1")
You will need one formula for each available option.
In the Master Sheet you can use these three functions to count the 1st, 2nd and 3rd most popular selections:
=VLOOKUP(LARGE(H1:H7,1),H1:I7,2,FALSE)
=VLOOKUP(LARGE(H1:H7,2),H1:I7,2,FALSE)
=VLOOKUP(LARGE(H1:H7,3),H1:I7,2,FALSE)
Hope this helps.
Good luck.
Tony

[cbell25]

Hi Tony,

Thanks for your response. Unforuntately I'm a bit more of a novice than that. If I give you the cell references would you be able to tell me the exact formula I could use.

In the master sheet I want to be able to show the number 1 option in Cell E15, the number two option in Cell E16 and the number 3 Option in Cell E17.

On each sheet there are drop downs in Cells E15, 16,and 17 and in each drop down there are 5 Options which for these purposes we will call Option A, Option B and so on. In total there are 8 sheets that the data would need to be pulled from and for the purposes of this we will say each Sheet is named Sheet1, Sheet2 and so on.

So, if for example the most common Option across the 8 Sheets was Option B, I would want the formula to recognise this and put 'Option B' in Cell E15 on the Master Sheet and so on.

Again, I really appreciate the help.

Cheers!

[Tony Valko]

You could do something like ARGK suggested and build a small summary table on your Master sheet then get the top 3 from that table.

We would need to know the actual sheet names to do this efficiently. Fictitious sheet names will only lead to more problems.

[cbell25]

Thanks Tony.

I managed to do exactly that and have got the results that I was looking for.

Thanks for your help everyone.

Cheers!

[Tony Valko]

Good deal. Thanks for the feedback!