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]

I understood what you were saying. Basically you are linking the master sheet to each employees sheet. If I have sheet 1 and sheet 2 and in sheet 1 cell A1 I put =Sheet2!a1 then if someone puts the number 123 in sheet 2 cell A1, then sheet 1 cell A1 will automatically read this. Therefore, you can do the same thing if they are in separate workbooks.

Let's say you wanted the master sheet, Kevin to update when Kevin enters his data. To do this, you would go to the master worksheet, go to kevin's tab within the master sheet and cell A1 you would put =filepath of Kevin's workbook, tab cell A1. It will look something like: ='H:\Projects\[Kevin.xls]tabname!$A$1 so you are linking the master kevin tab to Kevin's workbook and worksheet.
Then when kevin goes into his workbook and enters the number 2 in cell A1, the master tab automatically puts the number 2 in Kevin's tab in the master sheet.

depending on your filepath, that is why I said put the = sign. Have BOTH workbooks open. Open the master file, kevin tab, then go to Kevin's workbook and open it. In the master tab put the = sign (don't hit enter) Go to Kevn's workbook and click the cell you want to link and then hit enter. To test it, put a value in Kevin's sheet on the cell you just linked, then go to the master file and look at it and it should be updated.

Therefore you are linking the Master workbook and all the individual worksheets to each Employee's workbook and worksheet, so when each employee goes in to fill their own worksheet, in their workbook, the master sheet is automatically upadated for all employees.

Have you linked cells before? If not, try it on the same workbook and just link cell A1 to another cell in sheet2, then when that works, do the same but across workbooks.

 

[ChompGator]

Ok, awesome...

Ive got it working > Only problem now is...This is what is showing up in the cell on master.xls

#NAME?

But as soon as I entered the formula =file path(to kevins sheet)
it accepted it, its just that's what it started displaying, and suggestions? Could that be a formatting thing?

 

[ChompGator]

I read up on it, and apparantly the text Im seeing in the cell means Excel does not recognize the text in the formula...hmm - After I entered the formula you provided, excel changed the formula to this:
=H:\Projects\barb.xlsBarb '[Wheatley] Barb Wheatley'!$B$6

Then started displaying #NAME? in the cell

 

[angiemeh]

Before you try anything else, check to see if you have the Analysis tool pack installed- it's easy

Go to your Tool bar, click Tools, then Add-ins (you may need to scroll down) You should have the Analysis Tool pak and Analysis Tool pak for VBA checked do this and hit OK and let me know if that resolves the issue or not

 

[ChompGator]

Hey there,

Tried it out they were not clicked - > so I clicked them > but it didn't help .

- Jeff

 

[ChompGator]

Issue has now been resolved


Thank you so much for the help!!

- Jeff

 

[ChompGator]

Hello,

Now Im going to try and add some complexity to these sheets.

Im able to get each user to fill out their information and when they save their workbook, it automatically updates the master-workbook.xls.

Now here is the next issue: Say right now I list 5 rows for the employee to list their problems in, but say the employee has more than 5 problems, so he "adds a row" the problem is, in the row that gets added the formatting that is on the cells in the previous row isn't copied to the new row. And, how do I make it so if Kevin adds a row on the masterworkbook.xls under `Kevins` sheet it will automatically add a row...AND keep the formatting of the previous rows...

Would this be best solved by adding in a VB script, or what are some suggestions...

Thanks,
Jeff

 

[angiemeh]

If you want to keep it really simple, you could do a couple things:

1) Instruct the user to always copy and then insert copied cells when adding a row (this will keep the formatting- but you can't always get the user to follow directions)
2) 2nd option would be an auto close or auto open macro that if the user inserts a row (assuming there are no formulas on their sheet) you could use VB to just copy the top line with formatting and paste special formats all the way to the bottom of the sheet. You could even just record this and then add the code to an auto open macro so everytime the user opens the sheet the formats get copied

You could do the same for the master workbook with the formula error. On every open and/or every close you can copy the top line of each workbook and paste the formulas to the end of the workbook. This wouldn't be difficult to set up

Do you know VB?

 

[ChompGator]

VB, ah not well enough to write a script like this...Ive done a few simple things like 'click-able' links that when the user clicks on them, they get all company printers installed to their machine, real simple stuff like that...But as for this type of script, never attempted.

Regarding point 1, having the user just insert a new row, I could do that, but we have issues with people following directions, and each line is already numbered...the employee's workbook comes with several headings and 5 lines under each heading...I would like so: Say your James, and you need to insert a new row I want it so it automatically re-numbers the lines, and it will copy the formatting of the cell...Right now (I can send you the excel sheet if you wish) if I tried inserting a new row manually it doesnt copy the formatting, currently in the row there is no |cell 1 |cell 2 | cell3 |
there is no cell border lines showing...When I insert a new row > the line borders start showing up again... and I dont want that to happen.

So say (as I mentioned) your James, and you are writing under the "Problems" heading, your sheet has 5 rows, for 5 problems, you add a 6th row > it should copy the formatting and so that when saved the automatically add a 6th row to James sheet in the master-workbook.xls

Any suggestions?

 

[angiemeh]

That is why the user should copy a row and click 'insert copied row'. THat way it copies all the formatting, It would not renumber the rows, that is why if you had a macro that every time the user opened a workbook or a button that said reformat, then you could copy the formatting and paste it to all other cells.

Try this on one of your sheets:
1) Find a Button on the tool bar. If you don't have it, go to view-tool bars -forms
2) Click on the button and hold your left mouse key down and drag it to the white space of your excel file- you should get an option to record. Try it, hit record
3) Then copy the top line that has the formatting you want. If you have data from column A-Z, then highlight A1-Z1 if row 1 is the 1st row, if it is the 2nd row then A2-Z2 etc.
4) HIt Copy and then select the rest of the data and right click and choose paste-special - NOT paste. Hit paste-special then choose the radio button formats and hit enter.
5) Then hit escape
6) Then highlight where you have the rows numbered. Highlight at least 3 numbers (1,2,3) then drag that down and Excel should re-number everything.
7) Hit Stop recording (there should be a little box with a square that says this somewhere on your screen)
8) Now everytime you hit the button it will copy the formatting.

If this works (I can show you how to manipulate the code so you are always choosing all the data dynamically) but try this out first to get familiar with a button and creating a macro that copies the formats. Once you are familiar with this, your 1/2 way there.