• 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.

HLookup or Index or VBA code to populate data from different sheet to Dashboard

MaunishP

MaunishP

Member
Hi Team,
Initally data which i use to get was in single worksheet, where i had created marco which will umerge all cell names, transpose to different worksheet from horizontal to vertical. But now, as new work increased I have now only 1 workbook with 12 worksheet and different team members work on different task. Now challenge is I can still use my macro for every single worksheet but collecting data for 12 worksheets in a single dashboard is now time consuming.

Could some of you expertize, throw some highlights using any formulae, Index, Hlookup or VBA macro which will copy all data from all 12 worksheet into single dashboard. As shown below

Dashboard Format.png
 

Attachments

  • Queue.xlsx
    414.6 KB · Views: 2
Yikes...data mining nightmare. I don't suppose there's any chance you could convince the team to just have a single sheet with just 4 columns:
Sheet | Name | File No | No of Tasks

You could easily build all your reports then, including separate sheets for everyone, if it was this way. Going backwards...much harder.
 
Hi Luke,
But suppose if i have fixed 20 team members, will it be possible to use Index or Offset formula? If yes, than how :)
 
With your current setup, it will be nigh impossible. Each sheet has data going in both rows and columns. I'm not even sure what the dashboard is supposed to look like.
 
Hi Luke,
This is how dashboard is going to look like, all values from different sheet in single worksheet. Dashboard Output.PNG

Also i found a great article on this forum, where someone genuis like you helped someone for such thing using Index & offset formalu. Could you help to replica same here ?
 

Attachments

  • Queue.xlsx
    414.8 KB · Views: 0
  • Index Formula.xlsx
    74.2 KB · Views: 1
These are list of people only working for this dedicated task daily :

Aakash Patel
Amit Bhatiya
Baljindersingh Kang
Dharmesh Purohit
Hardik Shah
Hardika Patel
Monica Singh
Naeem Patel
Puneet Sharma
Purvesh Parmar
Rakesh Joshi
Uwais Pathan
Vishrut Thakar
Wasim Ansari
Yogeshkumar Sindha
Rizwan Ali Khan
Latish Panicker
Priyank Shah
 
Hi Luke,
As decrisbed in my first post that i have created a macro : here it goes : to check how does it work :

Open Queue File - Copy Cltrl + A data from DISCH COAF and paste it in Magic! file and press Cltrl + J and see output in Vlookup :

Will this help some thing for my request ?

Regards,
Maunish Patel
 

Attachments

  • Magic.xlsm
    322.4 KB · Views: 0
Hello MaunishP,

Not sure whether the attached file fulfills your request.. Let me know..

I have modified a bit to match the formulae..

Regards,
Pavan S
 

Attachments

  • Queue.xlsx
    415.5 KB · Views: 2
Pavan you are Genius !!!!! This is what i want :)

Now only question is if I look at formula from column D to Y, Match says = Dismissal COAF, do I need to replace this with all different sheet name ?
Will data still be selected from each different sheets. Yes currently it is showing. But if i drag formula with few more name it is giving error.

Any specific reason?

Capture.PNG
 

Attachments

  • Queue (1).xlsx
    417.5 KB · Views: 0
Hello MaunishP,

Couple of things to remember (in order to work with my version of formula)
1. Do not merge the cells - Better use Centre Across Selection

Note: In sheet DISMISSAL - COAF check the format in C5 Cell
What I have done is, I have unmerged C5:D5 and selected C5:D5 and using cntrl 1 --> Alignment ---> Horizontal ---> Centre Across Selection

Follow the same format in all the sheets for all the employees plz... or at a bare minimum use the format in DISMISSAL - COAF sheet

2. There are 2 formulas in Dashboard sheet.. One for File And another for Task so you may copy paste to for each of the blocks in order to work..

3.Match says = Dismissal COAF, do I need to replace this with all different sheet name ?

Ans: May not require if you take care of Do not merge the cells - Better use Centre Across Selection
 
Thanks Pavan. Last thing it there a possibility to reset macro ?

File which i have attached Magic! I require a reset button which will reset it to its default look and position are running it.

Is it possible ?

Thanks again in advance Pavan.

Regards,
Maunish Patel
 
Hi Pavan,
I have unmerged cell --> aligned them centrally. However still formula gives error. Please check
 

Attachments

  • Queue (1).xlsx
    417.9 KB · Views: 0
Hello Maunish,

You may have to relook on the data for each of the employee in all the sheets as I have fixed only the format.. Sry if it has caused you more work.

Regards,
Pavan
 

Attachments

  • Queue (1).xlsx
    420.4 KB · Views: 0
Hi Pavan,
Issue got resolved but sorry to bother you again, new issue generated that whenever i move this Dashbaord sheet to another Queue Workbook, formula still consists of Queue (1) book, it doesnt get replaced.

Is there a way to get it replaced ?

Please find attached 2 different workbook for you refrerence.

Regards,
Maunish Patel
 

Attachments

  • Discharge-Dismissal from Queue.xlsx
    410.2 KB · Views: 1
  • Discharge-Dismissal from Queue1.xlsx
    410.2 KB · Views: 0
@ Ninja's Could you also have a look here.

Only challenge now is when i copy dashboard sheet to another workbook, formula reference is selected from previous workbook.
 
Hello Maunish,

Either use replace feature to get it fixed for fix the formulas manually for 1st row and copy paste to other rows accordingly

Regards,
Pavan S
 
Hi Pavan,
I did tried to replace formula from first row and dragged till end, however still facing issue. Please find attached different workbook for your reference to test.

Even after replacing formula it still gives error or values are not correct.

Also I have done some alternation original sheet where, Dashboard and Master File need to be copied to new workbook before Dismissal - COAF and values are found correctly, however they are not found against correct users.

Sorry i am troubling you more.

Regards,
MP
 

Attachments

  • Discharge-Dismissal from Queue 021015.xlsx
    409.8 KB · Views: 0
  • Queue Dashboard.xlsx
    60.8 KB · Views: 0
Hey Buddy,

Any specific reason for moving the dashboard sheet to the other file? An advice I can give is to have a replica of dashboard sheet in the Queue Dashboard file which consists of output pasted as values rather than a formula..

Regards,
Pavan S
 
Reason moving dashboard to different sheet, team members will update data daily. Hence i will require to have a dashboard for each day :-( hence i need a universal dashboard where i will move dashboard in beginning of each day sheet and get output resulted.

Hope this information helps you :)

Regards,
MP
 
Hello MP,

I such case you may hide the dashboard sheet and share the file to collect the inputs from different team mates and at the time of consolidation you may unhide and move the dashboard sheets data to another file (instead moving the dashboard sheet itself).
 
Makes Sense. I can start this from today itself, but i need to prepare data review for last quarter. Try to help out here

Also, I tried to use Index & Match formula, i was succesful at start but didnt saved file and later tried again, values i am not getting.

Please Help out for both issues.

Regards,
Maunish Patel
 

Attachments

  • Book1.xlsm
    245.3 KB · Views: 0
Yuppy Yuppy Great worked Perfecto ... 1000+ Likes to you Pavan.

HatsOff !!!!

Now Lets take a sitution> Regarding you have use Range as Sheet #, if i have fixed sheet names. How to Replace them ? I have highligthed it.

Capture.PNG
 
You must log in or register to reply here.
Back
Top