Retrieving Data Using Excel

[ChompGator]

Hey,

I have a `master` listing that has a list of material on it.
Lets call it test.xls

Each employee has their own work work-book with only one sheet inside the work book, lets call this test1.xls

I want users to be able to add information in test1.xls and the list test.xls automatically gets updated

What kind of formula do I use for this?

- Jeff

 

[angiemeh]

If I hear you correctly, your master list has all items, but each individual workbook may have a sub-set of those items? If the master list is all items, then you should be able to do a V-lookup. Put the V-lookup formula in your master file and grab the updated data from each of the workbooks.

How many individual workbooks do you have? I did something like this a while back. I had 5 different workbooks and I needed my Vlookup formula to know which workbook to go look at. I could do this with one formula. Let me know if this is what your looking to do and I can explain further.

 

[ChompGator]

Go ahead with the Formula, - there are 15 workbooks.

And this is how it will be set up (just an example) - This is how the master work book will look.

Richard
<Richards information coming from his own workbook>

John
<Johns information coming from his own workbook>

Kevin
<Kevins information coming from his own workbook>

Smith
<Smiths information coming from his own workbook>

Each users information comes from their work book that they fill out and just save...Once they save their individual work book, if I want to find out any of their information, I just look at the Master Workbook, instead of me having to go look through individual workbooks.


- Jeff

 

[angiemeh]

How are the items organized? I'll need to know a little more about the data, but I'll give it a shot

Master file:Lets say you had item# and then Description and then each employee would update their descriptions (I'm assuming each employee will have a subset of items and the item will not be shared by multiple employees)

- You would need to put a column Employee and then put the Filepath of the employee in a column, so you would put 'C:\Filepath\[Test1.xls]Smith' Whatever your filepath is. You will name the individual file exactly what you have in this column, so for Smith, he might have workbook called test1 and tab named Smith and Kevin may have workbook name test2 and a tab named Kevin etc..

Then, you do a vlookup formula:This also assumes every individual sheet is set up the same, so if Item# is in column 1 and Description is in column 2, that will be true for all individual tabs

Example of Master file set up
A..............B.............................C...........................................
Item#..... Description...............Employee..................................
123 ..... enter formula here.........'C:\Filepath\[Test1.xls]Smith'
234 ..... Black......................'C:\Filepath\[Test2.xls]Kevin'
345 ..... Silver......................'C:\Filepath\[Test3.xls]John'
234 ..... Orange.....................'C:\Filepath\[Test4.xls]Richard'
343 ..... Blue and Gray...........'C:\Filepath\[Test5.xls]Smith'

Let's say you wanted description to update in the master file. You would enter the formula in cell B2 :

Cell B2 = Vlookup(a2,(INDIRECT("'"&CONCATENATE(c2)&"'!$a$2:d5000"),2,false)

So you are looking up Item 123 within the Smith file and pulling back what is in column 2

To make it easier (if possible) you could have all the tabs on one worksheet, so you have a File, workbook and all individual employees as tabs instead of separate workbooks, that way your file path would remain constant and entering the filepath would be easier.

I don't know if this is what your looking for..

 

[ChompGator]

Ok, Im going to be giving this a shot right now, Ill respond here with any problems or issues I may have or encounter.

Thanks for the help!

 

[ChompGator]

Ok, it is starting to come together...But Im having a problem here:

In the master workbook on sheet1, line 24 cell B - I want to add the information from the work book marcia.xls sheet 1, line 2 cell A

What is a formula to do that?

 

[angiemeh]

It depends. Is cell B24 on the master sheet always going to reference Maria's sheet? And on Maria's sheet, will it always be the same cell or will it change?

If something will always be the same then in cell B24 you can just put an = sign and then go to marias sheet, click on the cell you want and hit enter and the master cell in B24 will always reference that cell in maria's sheet.

If you could tell me a little more about the data that would help. What is column A & B in the master sheet and what is column A & B in each of the employee sheets?

 

[ChompGator]

Hey,

Thanks for the help so far, Ill explain more...Now keep in mind we're not dealing with individual Sheets, we're dealing with two separate work-books and multiple sheets inside of one of the work books.

The two work books are - master.xls
and the rest of the work books are named after the 15 employee's in the department, so there is one for Richard, Timothy, Kevin and so fourth.

Inside of master.xls there are 15 sheets named after each employee.

Then each employee has their own .xls that they fill out...However every employee's .xls sheet looks the same. So to answer one of the questions, yes B2 will always reflect the same thing, C2 will always reflect the same thing.


So say your Kevin and you update your .xls...Then John updates his .xls...

Now Im the boss and I want to view employees sheets...So I open master.xls and I click the "Kevin" sheet/tab below at the bottom and the information displayed on the sheet that Im viewing reflects the information that kevin has filled out and saved on his seperate work book.

So as I said inside master.xls there will be 15 sheets/tabs, one for each employee.
Each employee will have their own seperate sheet that is not apart of the master.xls

So once a user updates his personal sheet, it automatically updates the master.xls

 

[angiemeh]

I think your solution is much easier. All you have to do is link the cells.

So in master.xls sheet Kevin, just put = in cell A1, then click on A1 in Kevin's sheet and hit enter. Then look at your cell in A1 in the master and take off any $$, so if it says $A$1, change it to A1. Then in the master sheet copy that all the way to the end of where your data should be. That way you have an exact copy of Kevin's sheet, so whatever he does to his sheet, the master sheet tab Kevin is constantly looking at Kevin's sheet. Do the same for the next person, say Joe. in the Master file, link tab Joe to Joe's sheet just like you did for Kevin.

Do this for all 15 employees and anytime you go into the master sheet you will see all current updates.

Also, you can take it a step further and create a pivot table in the master sheet that links all 15 individual master tabs into one data set so you can slice and dice your data.

 

[ChompGator]

Hey there,

I get what your saying, but I think Im not explaining it well enough (lol).

I have 15 individual work books all located here:
H:\Projects\james.xls
H:\Projects\kevin.xls
H:\Projects\smith.xls
(and so on...one for each employee)

Then the last workbook is:
H:\Projects\admin\master.xls

In the `master` workbook there is 15 sheets inside of it, one for each employee, but the employee's cannot edit, or view the master workbook. Only the boss can. The information that is displayed in the master workbook comes from each employee's .xls - All the employee's excel documents are the same, the only thing the employee can do is fill in the blanks on their work book. Once they save their workbook, it updates that specific employee's sheet in the master.xls work book.

So in the last message, when you mentioned, 'Try putting an = sign in the master work book, then going to an employee's sheet and clicking the cell and hit enter' I did try that, but it didn't work... but thank you for the tip.

So now that I think I have explained it better (finally! lol) - What is your recommendation?

Thanks again for the help, - Im going to send you a private message incase you prefer to send an email to me instead of these forum messages!