Jump to content


Photo

vba or vlookup

VLOOKUP pivot table cloud

  • Please log in to reply
5 replies to this topic

#1 Peggy

Peggy

    Newbie

  • Members
  • Pip
  • 8 posts
  • LocationMiami, Florida

Posted 14 February 2016 - 05:21 AM

How do I pull data from a workbook, to another workbook.  I collect data on a daily basis for agency personnel that are assigned to sit with a patient on different units within the hospital.  Each unit has it own budget, therefore I need to pull the data I collect for each unit separately . So column A contains the date, column B contains the Unit they worked on, column C contains the room #, and so on and so on.  So in another workbook I need it to pull all the data I collected for each unit.  Worksheet 1 unit "7Pavilion, worksheet 2 unit "6Pavilion", worksheet 3 unit "ICU" and so on and so forth.  I also want this to happen as I enter the data, it automatically pulls the information to the other workbook.  I hope I explained it right.  I have attached the workbook I want the information pulled from.

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 14 February 2016 - 04:55 PM

Hi Peggy,

 

From your description, it sounds like you'll either need to use VLOOKUP or use a pivot table. I've taken a look at your attached file, but I can't figure out what you're trying to do. If you can give an example of what result you're trying to get I should be able to give you a more specific answer.

 

You can find a video tutorial explaining VLOOKUP in: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

For the video lesson showing how to create a pivot table, see: Expert Skills Lesson 5-1 Create a pivot table.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Peggy

Peggy

    Newbie

  • Members
  • Pip
  • 8 posts
  • LocationMiami, Florida

Posted 16 February 2016 - 12:05 AM

I have to create a share drive with a folder for each unit, in each folder I have to put their agency sitter data I collect for the month. I could go into each folder and enter each agency separately, but that would be so time consuming.  I enter it on a daily basis. e.g: Copy of Agency FY 2015.  So I want to know if there is a way that when I am entering it can automatically go in to each appropriate folder.  Is there a formula I can create in each folder, so it will pull the info from my file as I type it?  I hope that makes sense, it seems so hard to explain.  Let me know?  Thank you again for you time.



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 17 February 2016 - 07:36 PM

Hi Peggy,

 

It sounds like you have lots of separate workbooks that contain the same data, so the best solution might be to consider changing your approach.

 

If all of your data, for all agencies, was held in a single workbook, you'd only have to enter the data once and could use pivot tables to create summaries for each agency.

 

Pivot tables are explained in the video tutorial: Expert Skills Lesson 5-1 Create a pivot table.

 

I hope this helps, but please feel free to reply if you need any more help.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Mike

Mike

    Advanced Member

  • Administrators
  • 175 posts

Posted 18 February 2016 - 10:57 AM

Hi Peggy

 

I also looked at your sample file to try to understand the problem.  It occurs to me that there may be a more efficient cloud-based solution to the way you are currently distributing your data.

 

The Problem

My understanding of the problem is that you have a table containing multiple rows of data.  One of the columns contains a field called "Unit".  You need to distribute a subset of this data (filtered to a single unit) to each unit each day.  

 

The Current Solution

You are manually copying the data (for each unit) from a master workbook into one workbook per unit.  You then place these workbooks in shared folders so that the each unit has access to the data.

 

Data Structure Issue
Though unrelated to the problem, I would advise that you consolidate your Agency FY 2015 workbook into a single table rather than have a different worksheet tab for each month.  It will then be very easy to filter by month if you need monthly summaries.  The advantage of this approach is that you’ll be able to perform monthly analysis on the current and past months via a single pivot table.  You'll also be able to produce quarterly and annual summaries with just a couple of clicks when your data resides in a single table.  Pivot tables are extensively covered in: Expert Skills, Session 5: Pivot Tables.

 

Potential Solution

There are many solutions to every problem.  It is often the case that you find yourself focusing upon the implementation of the solution rather than the actual business problem.  The problem is that each unit needs up-to-date (ideally real-time) information about their activity.

 

It is unclear whether the data is confidential.  In other words, whether one unit is not allowed to see what the other units are doing.  For the moment I’ll assume that there is no issue regarding one unit seeing another unit’s data.

 

If this is the case why not publish a pivot table via the cloud that personnel with the right password could access using any web browser (including pad devices or mobile phones).

 

Publishing to the cloud is extensively covered in: Session 8: Cloud Computing, OneDrive and Excel Online.  The OneDrive will automatically synchronize to your local file so your users will see your data seconds after you enter it into your workbook.

 

You’d end up with something like this:

 

For Forums.png

 

Units could then double-click on the total hours’ figure to see individual transactions.

 

You could then add slicers to enable users to summarize and filter data using simple touch controls by whatever criteria was useful.  

 

Filtering pivot tables using slicers is covered in: Expert Skills, Lesson 5-8: Filter a pivot table visually using slicers.

 

If you wanted to base the pivot table upon a new consolidated table showing all months and all years you could also provide a time-line control to enable users to review any past month, year or quarter's performance.

 

Adding a simple time-line control is covered in: Expert Skills, Add a timeline control to a Pivot Table.

 

And adding a more advanced set of timeline slicers is covered in: Expert Skills,  Lesson 5-10: Use slicers to create a custom timeline.

 

Everything would then be real-time and you wouldn’t have to do a thing to provide up-to-the-second information to all of your users.

 

Best Regards

 

Mike Smart


Mike Smart is the author of ten world best-selling Excel books. The books are available in printed form for for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Mike has also recorded over 850 video lessons (that you can watch online) for Excel 2007, Excel 2010 and Excel 2013.
Mike Smart is also part of the team that answers questions posted on the ExcelCentral.com forums.


#6 krisogbe

krisogbe

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 13 March 2016 - 06:02 AM

Hi Peggy.

 

I guess what you want is to be able to break your dataset into the various units on your worksheet and save/publish for each unit.

 

What you would do is to have one sheet for each month (or for the whole year if you want) for capturing your data.  At the end of the period you do a pivot by:

 

1. put your unique identifier (Unit) on the FILTER of your pivot

2. put the other fields on ROW

3. put your Amount on the COLUMN

4. click to your ANALYSE menu

5. on the left side you will see 'Option' pull down button

6. click the pull down 'Option' button and click 'report filter page'

7. double click on 'Unit'

8. this will break your report into the various units on each tab for each unit on your worksheet

9. you can then move each copy to save for each unit.

 

Hope this helps.  Below is a screenshot of your report broken into tabs automatically (sorry not allowed to post image)

 

Goodluck.

 

Chris.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users