Check Multiple Sheets For Specific Data

[xybadog]

Hi

I need some help with a formula and I've attached an example.

What I want to be able to do is check multiple sheets in the same worklog and summarise that data in a list on a single sheet.

So, in the example, I want add a formula in to "Summary" worksheet to check Sheet1 and Sheet2 for a specific value in Col C. Where a match is found the data from that row would populate Summary worksheet with a list of all rows in Sheet1 and Sheet2 where there's a match.

I know how to do this when referencing just one worksheet but not with multiple sheets. I may have up to 20 different worksheets I need to reference but I've tried to keep the example simple.

[oeldere]

Maybe you can use a macro to summerize all worksheets in one.

After that you can use pivot table, to analyze your data.

[xybadog]

Is that the only option? I've never really done any VB before so would prefer the formula option if possible.

[daffodil11]

And here's a possible VB option.

VB is easier to use than to write. The hard part is really just knowing the exhaustive dictionary of possibilities, of which I'm familiar with maybe 1/100th. (And yet somehow I still limped through this )

This simple code I've thrown together takes whatever you put in Cell D1 on the summary, and goes and finds it in column C of every other tab and lists out every match.

To use VB, simply copy the code below, go to Excel, hit Alt+F11, Insert -> Module, paste the code in that window, and then close it.

I've added notes through the code so you can see what it's doing. Ideally, you'd just change the range in "For Each c In ws.Range("C2:C5")" to meet your exact layout.

Please Login or Register  to view this content.

Something like this can be made as sleek and cool as you want. You could even have it prompt a pop-up window to ask you which Serial Number to go pull.

[xybadog]

I can't get this script to work. I've changed the value in D1 on the summary sheet and the data doesn't update.

[daffodil11]

Did you run the macro again?

Hit Alt+F8, and run it.

Alternatively, you could go to Insert -> Shapes, draw something like a circle, right click it, and assign the macro to it. Now you just click the circle to run it again.

[xybadog]

Doh. Forgot that Told you my VB knowledge was non-existent.

Is there a way I can get this script to autorun every time a new value is entered? Apologies if this is a very basic question.

[daffodil11]

Everything is possible, some things are just hard. I'm pretty terrible at VB, but I know enough tricks to do this too.

Instead of using that code in a standalone macro, we instead turn it into a worksheet_change event.

Open VB Editor (Alt+F11), and go to the left side of the screen and double click the Sheet1(Summary) object in the little tree thingamabob.

This is that sheet's code. We set the target range to D1 in the code, and now every time the worksheet is changed, if the change occurs in D1 the code will fire off again.

Please Login or Register  to view this content.

[xybadog]

Thanks for your help.

[daffodil11]

Thanks for the feedback, glad I could help.

[xybadog]

I've actually had time to test this script now but it doesn't work as I intend. If I change the value in D1 it just appends the result and doesn't clear the previous data.

So, I've created a more specific example (attached) if someone could please give me some advice.

On every worksheet, except "Summary" and "Total", I want to identify where the value 1 occurs in D2:D5 and list the data from all columns on those rows on the Summary tab.