Help on IF on multiple worksheets

[cheelong1997]

Hello all,

I am trying to build a worksheet that will read multiple worksksheets to add/sum up data. The example I have attached shows the "Car Sector" and the 2 services, "PS" and "none", I used an IF statement in column A to read the data for each company, any company with the "PS" written in its name would have had "Services" provided, companies without this we provided "Other".

What I have are multiple sheets such as "Truck Sector", "Airplane Sector" and so on that have similar data as the screenshot and I want to build a worksheet that will list the "PS" (and "Other") services provided such as below in a column but it would have added up for each month the "PS" and "Other" in each of the sheets in the whole workbook....not sure if this is possible?

Car Sector
Truck Sector
Airplane Sector

Hope this makes sense, any help greatly appreciated!

[TMS]

Having multiple sheets is making a rod for your own back. As you find, there comes a time when you want to analyse or summarise the data ... and it can be difficult and time consuming.

You would be far better adding an extra column, probably at the left of the existing data, and adding the sector to each record. Then you can copy all the data into one worksheet. Once the data is in one sheet, you could consider converting the data to a Structured Table or, at least, switching on Autofilter. You can now sort and/or filter the data and select the sector(s) and month(s) you are interested in. For a more detailed mix and match analysis, you can use a Pivot Table.

The other advantage is that if you do want to add, remove or rearrange the columns, or edit a formula, you just do it once on one sheet.

You will be amazed how much easier it is.

If you need any specific advice, upload a sample workbook. Or get in touch ... we're only about 10 miles apart ... small world!

Regards, TMS

PS: pictures are next to useless although they obviously give some visual clues, there's not a lot you can do with them.

[cheelong1997]

Wow, it is a small world! thanks for the reply, attached is an example of what I am trying to do, I have only looked at "Car" sector to try to figure out an easy way of doing this...in our proper sheets there are probably 20+ worksheets with a lot of data to wade through...any help greatly appreciated!

[samba_ravi]

Sectors (Service and Others) were not found in Airplane, Truck and Industrial Sheets

[cheelong1997]

Yes I forgot to say, I hadn't added in the extra column to differentiate apart from the "Car sector". Any of the companies with PS is Service - I was hoping some sort of formula could read all the worksheets to pick out the "PS" in the company names to make life easier!

[samba_ravi]

Can i remove column a for car sheet too

[cheelong1997]

Sure, of course you can!

[TMS]

Please see the updated sample workook.

I've added the columns and your formula. Then copied them all into a single worksheet, converted the data to a Structured Table and created a Pivot Table. The Pivot Table is filtered to just show Services for the Car sector. As it stands, it is listing the companies but you can click on the minus sign to close that up.

When you need to add columns for June, July, August, etc. and then propagate your formulae, I think that you'll find doing it once is a lot less time consuming than doing it for each worksheet for several/many sectors.

Enjoy!

Regards, TMS

[cheelong1997]

Thank you very much, I will have a look later!

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.