AUTOMATIC PROCESS TO FIND THE VALUE IN ALL THE SHEETS

[juanloj]

HI ALL,

Thanks in advance for your help, just even reading will help
I am at Excel, but really far from your experience.

I have a worksheet with many sheets, 52,one per every week in the year.
There is a colum with "Total" in every spreadsheet , normally in column "M" or "N"( can be modified to get all in column "N")
In "Sheet1" I woud like to have a summary with the Job Ref and the Total on all the weeks in all the worksheets
I would be able to do it manually with Vlookup and search the "Job Ref." but it is a lot of work as the sheets have not correlative names.
I will attach a sample.

Please let me know if you need more information

Thanks for your help
JA

 

[Nebu]

Hi:

Refer the following link for a solution

https://www.myonlinetraininghub.com/excel-3d-sumif-across-multiple-worksheets

Thanks

 

[vletm]

juanloj
Of course, You Could have all weeks in same sheet?
... and use eg Pivot-table to get Your summary
... without extra formula.

 

[juanloj]

HI vletm

Thanks for your reply, the link you sent to me is working
There is a lot of data in every week/sheet , I cant merge all the weeks

 

[vletm]

juanloj
How much is 'a lot of data'?
Have You tested eg Filter-option?
Do Your every week's sheet has same layout?
... why You cannot?

 

[juanloj]

Hi vletm

Every week, it is the same layout, you can see in the sample I attached
It is not for me, but it is not possible to merge all the information in one sheet
I think about pivot table initially, but I will 52 pivots table, unless you tell there is another mehod

Thanks

 

[herofox]

you can use this

=SUMPRODUCT(SUMIF(INDIRECT("'"&$M$3:$M$6&"'!b3:b2000"), $B3,INDIRECT("'"&$M$3:$M$6&"'!n3:n2000")))

 

[vletm]

juanloj
Okay - You didn't answer to questions.
I won't have or offer to somebody 52 (53) same layouts.
Someone else will help.

 

[juanloj]

Hi vletm
Sorry, I am out of the country leading a project and didnt have time to follow up your reply
I attached the sample that represents 4 of sheet of the real data, if you have a look at the sample you will see the amount of data
The layout can change every week, in terms or adding/decreasing more clients (rows) or less/more workers (columns).
But the column can be adjusted to be always in "N" leaving blank column and maybe the same for the row.
However we dont know what is gonna happen in the future there could be more workers and clients
I am not sure how to test the filter option
Do you suggest to create another sheet and copy all the sheets there and then run the pivot table?

 

[juanloj]

HI herofox

It is working very well, we have the same approach
The only difference is the way you were adding all the sheets in the formula

Thanks a lot

 

[vletm]

juanloj
Here SAMPLEs of Your 52week sheets.
For me, there would need to make (someone would write minor) modifications.
eg hours are hours ... and ... if there should notice someway dates ... weeks ... months ... then there should be also dates.

Now, I used only top two parts of HOURs ( I don't know, what kind of clock do You have? )
... and tested QUICK Pivot-table (weekly and total) = not ready at all.
Many of Your sheets 'calculations', I didn't notice with this SAMPLE.
Try to keep on breathing ...

 

[juanloj]

Hi Vltem

I think your proposal is great . In my opinion it is also a more organised way to get the data presented.
I also think initially about pivot tables, but didnt know how to compile all the data in one pivot table
I know some managers would like "their own way", I will ask my girlfriend, it is for her job and see what she thinks

Thanks a lot for great help!

 

[vletm]

juanloj & girlfriend
It's a sample ... sample ... sample!
Some other 'minor' modification would need to do ... before that could use.
Many things depends eg
... what needs? ( it's different than 'nice-to-see-or-have' )
... how to use?