Extract Data from multiple sheet

[simran555]

Dear All,

Iam working on a master file that will extract working day of people who could be working in different locations. The formula to extract data looks like this ;

=(IFERROR((VLOOKUP(B3,'C:\Users\p.simran\Desktop\01-January.2020\02-DDS010\[01- SALARY - DDS010 (Main File).xlsx]SIM'!$D$6:$J$1000,7,0)),0)+(IFERROR((VLOOKUP(B3,'C:\Users\p.simran\Desktop\01-January.2020\03-DDS026\[01-SALARY - IDS026 (Main File).xlsx]IDS026 (2)'!$D$5:$J$1000,7,0)),0))+(IFERROR((VLOOKUP(B3,'C:\Users\p.simran\Desktop\01-January.2020\06-IDS (003, 004, 014, 015, 016, 017, 018, 019, 020, 021, 022 ,Al Sag, Accomodation)\[01-SALARY - IDS(All EXCEPT DHOW, IDD010, IDS023, IDS024, IDS025)..xlsx]IDS-GENERAL'!$D$6:$J$1000,7,0)),0))+(IFERROR((VLOOKUP(B3,'C:\Users\p.simran\Desktop\01-January.2020\05-IDS025\[02 - SALARY - DDS025 (Main File).xlsx]IDS025'!$D$6:$J$1000,7,0)),0))+(IFERROR((VLOOKUP(B3,'C:\Users\p.simran\Desktop\01-January.2020\04-IDS023 & IDS024\[01- SALARY - IDD023 & IDS024 (Main).xlsx]23&24'!$D$6:$J$1000,7,0)),0)))

All the above sheets are located in their respective folders and cannot be clubbed together in one workbook. kindly advise

[Richard Buttrey]

Dear All,


All the above sheets are located in their respective folders and cannot be clubbed together in one workbook. kindly advise

Whyever not?

A macro could be written to populate all the data into a single worksheet and once that's in place analysis is simplified immensely.

[simran555]

Could you please help me out in forming the macro

[Pepe Le Mokko]

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been only some minutes since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[simran555]

Yes, i get your point. I don't understand why would i even put pressure on someone. So don't worry about it and i am very well aware of a member's obligation towards their work or whatever is their area of concern.

Thanks

[Richard Buttrey]

The file names do not look consistent is that the case?

The 3rd file in the formula:

Please Login or Register  to view this content.

Looks strange. Can you explain that please? Especially the bit about ..."ALL Except..." I just can't see how all that works as a formula! It also seems to be the only formula that doesn't reference the 'main file'.

A list of the workbook names to be referenced would be useful and for ech workbook the names of the sheets in that workbook that you want to include.

In addition would you confirm that all the sheets are consistently laid out. i.e. the columns with the same data are always in the same place.

It would also help if you could upload an example of one of the workbooks.

[simran555]

So all these sheets have working days, with same kind of formation. The master file will have all the employee data along with their emp. code and if i can vlookup the emp. codes against the data in the attached sheets, it should add the working days of the employees available in the sheets.

I hope i am clear.

[Richard Buttrey]

Thanks.

1 How many workbooks are there in total?
2.Do they all have just one sheet
3. Is the filename always consistent. i.e. "IDS" followed by a three character string identifying the day of the year e.g. "001",...... "365"
4. What is special about the "IDSGEN" workbook? The example you've uploaded seems no different to a daily sheet. How many of these special cases without a day reference are there

The solution I was proposing was a macro to load all the sheets into one master database on a single sheet, with an extra column to contain the date of each record.

Once that's in place you can easily analyse the data. Simple SUMIFS formulae would of course work, but with a database like this a Pivot Table is much more efficient and elegant and will have much more flexibility.

[simran555]

1. It might increase in time.
2.Yes, All of these projects have a separate sheet
3.It keeps on changing - IDS means Industrial services ( Term used by the company)
4.All the sheets that were uploaded were payroll sheets which had too much data. So i refined the data basing on the criteria that's needed at this moment.
5.The reason to collate all the data from these payroll sheets onto to a master sheet is to get the sum total of working days ( As staff tend to move around as per the project's requirement). The master data itself will have the employee data like the Emp. code, name and their salary breakdown.

I tried using pivot chart, and it does sum the working days but it also sums up an employee's salary, ID No. ( If the employee works in more than a location)

[Richard Buttrey]

I need to understand the complete 'map' of the file names, sheets within files and the naming convention. Some seem to be in a "Main file" and some in a "Main" file.

Most seem to carry the day of the month, e.g. 023, 024 ..etc, but there are some named "Al Sag" & "Accommodation"
The names of the sheets in the file vary. Sim!, IDS-General!...etc.
Presumably this is an incremental system such that if you've already loaded say day '025' into the database, tomorrow you will want only want to load '026' rathet than start from scratch each day and load everything.

Is it not possible to standardise and carry the two character number of the month rather than the month name in the name of the file? e.g. 01....12

The details of the stuff your dealing with in your first post are just very hard to understand


Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out". We're usually quite good but not prescient.

Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

If you can let me have a detailed specification which describes completely ALL the files in question, where they are, how the names may vary from month to month, e.g. when and how does say "IDS" change to something different, how you use this system (every day or periodically), then I may be able to help further but without a detailed spec. I know from experience so much time will be wasted until I finally understand the task.

[simran555]

Dear Richard,

I hope you are doing well

I completely agree with what you said above, and I would love to explain to clear any ambiguity. Please disregard my first post, in my second post I cleared out the huge file names (any name can be kept as a matter of fact).

All these sheets are individually placed in their respective folders and contain names of staff working there along with their working days. All these sheets are placed in the corresponding payroll month (say Feb.2020) and under this folder there would be master sheet file (which I will be using to collate data from all the other sheets). The other folders in this folder will be IDS10, IDS025, IDSGEN. so, on and so forth; it won't be more than 5.

The sheets will be used once in a month to add new employee data along with all the necessary details in order to process their salaries. All i need is the master sheet to collate data (The total working days of employees, who tend to work in more than a location) and display it under the working days column. I have the master sheet format as well for better understanding of the task in hand.