Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel 2013:  How to pull data from one worksheet into another automatically

Posted on 2016-09-14
9
50 Views
Last Modified: 2016-09-29
Hi,

I am using Excel 2013.  I have a very simple .xls worksheet which records our vehicle inventory, it is over 1000+ records.  The columns are:  
Make; Model; Year; Licence_Num; State_Registered; Reg_Expiration

I want to pull data from the first worksheet into another worksheet automatically by using the State_Registered name.  (all records have the normal abbreviations TX, NM, ect and are consistent).  I have used Vlookup and the basic filtering, which works fine.  But the total number of vehicles changes monthly, which makes trusting the data questionable.

When New Month Inventory list is completed.  I would like to copy-and-paste the new inventory into the first worksheet and when I go to the corresponding state tab, the records for all State_Registered records will be updated automatically.

Any suggestions will be helpfully.  Thanks for your assistance.
0
Comment
Question by:stepnharp
9 Comments
 
LVL 16

Expert Comment

by:Edward Pamias
ID: 41798215
If you use the offset command that will adjust for data adjustments.

=offset($a$1,0,0,counta($a:$a), 12 <-- example

If you create a name range (CTRL-F3, click new)  and put this in there, where 12 is, that is the number of columns in the data sheet, you can change that number to how many columns you have. So instead of highlighting all the data, for the Vlookup, use the name range instead.

If you had a sample sheet I could try to set it up for you.
0
 

Author Comment

by:stepnharp
ID: 41799026
Edward,

Thank you for your assistance.  Please note besides watching a youtube.com video, and then following it, my excel experience is limited.

I have attached a sample .xls

Thanks Scott
VehicleRegistration.xlsx
0
 
LVL 8

Expert Comment

by:Koen
ID: 41800444
I have done 2 things for you (see attached sheet):

- I created a table holding your base inventory list
   The advantage is that when adding lines (cut and paste new values) the table will auto expand
- I created a pivot table on the second sheet, with a top level filter for the state
   The only thing you need to do when updating the inventory, is place your cursor in the pivot table, right click and hit 'Refresh'. Since the source is the (auto-expanding) table, it will automatically have the new records
VehicleRegistration.xlsx
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:stepnharp
ID: 41804837
Koen,

This looks good.  Thank you for your effort.  

Can you tell me

1.  how to modify the Pivot table so that I have 11 columns?  
2.  How to get the State value to show up also in column 4?  I will print off the final results and need the State value to show up on column 4.

I have youtude'd Pivot tables, but a have not been able determine how you were able to get the information to display as columns, every time I do it the columns are moved to the rows.  I need something that looks exactly the same as the Inventory tab, but is filtered down to specific records.  

Thanks, Scott
0
 
LVL 8

Expert Comment

by:Koen
ID: 41804948
I don't understand your first question... 11 columns? where am I to find these 11 columns...

On the second one... I'll send you a new file with two possible solutions later tonight.
0
 
LVL 8

Accepted Solution

by:
Koen earned 500 total points
ID: 41805624
The pivot table can filter on the top level (as in my example) to give you the list state per state...
You cannot however use the same column twice (once in the filter and once in the report itself)

there are several outs here...
1. you could just filter your original list.
  As you can see, after turning it into a table, you have the filter boxes in your header...Selecting the state you want, gives you the list you want state per state. Printable and original format
2. If you insist on the pivot (as i have used it, you'd need to double the state column to have it once in the filter and once in the report. Now this is not a lot of work, since you can just put the formula in there and it will auto-copy when records are added (if added it to the new sample attached for you to find)
VehicleRegistration.xlsx
0
 

Author Closing Comment

by:stepnharp
ID: 41820731
Koen,  Sorry for the delay in responding.  Thanks for your assistance.  With your examples, I have been able to get the results that will work for me.

Peace, Scott
0
 
LVL 8

Expert Comment

by:Koen
ID: 41821118
Glad i was able tot help! Tx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41821323
@Koen -
You cannot however use the same column twice (once in the filter and once in the report itself)
 Yes you can!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question