• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

AUTOMATIC PROCESS TO FIND THE VALUE IN ALL THE SHEETS

juanloj

New Member
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
 

Attachments

vletm

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

juanloj

New Member
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

Excel Ninja
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

New Member
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

Active Member
you can use this
Code:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$M$3:$M$6&"'!b3:b2000"), $B3,INDIRECT("'"&$M$3:$M$6&"'!n3:n2000")))
 

Attachments

juanloj

New Member
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

New Member
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

Excel Ninja
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 ...
 

Attachments

juanloj

New Member
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

Excel Ninja
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?
 
Top