stepnharp
asked on
Excel 2013: How to pull data from one worksheet into another automatically
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.
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.
ASKER
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
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
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
- 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
ASKER
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
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
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.
On the second one... I'll send you a new file with two possible solutions later tonight.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Peace, Scott
Glad i was able tot help! Tx
@Koen -
You cannot however use the same column twice (once in the filter and once in the report itself)Yes you can!!
=offset($a$1,0,0,counta($a
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.