Excel program with multiple tabs with shared information linked

[Malinko9]

I need help on writing an excel program that will allow my company to have multiple tabs with some of the same information linked. I would like to have the people in my company each have a tab with there information on it, and when they change something in there tab the main tab with the master infromation will also change. Is this posible?

 

[russ_watters]

Yes. The easy way is to start writing a formula, then while you are in the middle on it, click the tab you want and click the cell you want to reference. The cell address will appear in the formula.

 

[Malinko9]

I don't even know how to start a formula like that. Plus if the formula is in the cell that the information is in would you not over type the formula? Let's say you have a total of four tabs on the bottom, One called Master, 2 called joe, 3 called tim, and 4 called sam. The point of this work book is to keep track of dates that employee did something. I would like to have the cells open to write dates in but still have the three named tabs link to the Maseter. so let's say joe put a date of 2/22/04 in the column called Group check (b10) I would also like the same information in the master to change. I tryed the copy, paste then link cells, but that still puts a formula in the cells you linked, I would like to get rid of the formula so you can type in ether cell.

 

[binzing]

Basics on starting formula:

Always starts with =

Then you do an open parantheses.

(example: a1:a5 * b1:b2 - c1)

It's pretty straight forward. Just play with it. Also, you can click and drag to select the cells you want while writing a formula.

 

[russ_watters]

Type an equals sign, then click on the cell you want to reference. That's all there is to it.

 

[Moonbear]

I don't think you can do what Malinko is asking in Excel. S/he's asking about being able to type in EITHER cell. With a linked formula, that only works if you do all the entry in ONE of the two worksheets. It also gives you no flexibility if multiple entries in one workbook get added into a singly entry in another, particularly if those entries are not fixed in number. It would also be very cumbersome to do with multiple entries for multiple employees, because you'd have to manually create the formula for each field in the master sheet.

Malinko, why are you using Excel for an application like this? It sounds like you need a database software where you can define fields from individual database entries all be linked to a master record.

Another possibility...do they need their own sheets, or could they modify a master sheet? If you just need them all to see all the entries, and you're using MS Office, then have you considered using MS Groove? It creates a collaborative environment where you can "publish" a version of a file for the entire group to use and every time a member of the group modifies it, it automatically changes everyone's copy of it. I haven't actually tried it myself, because nobody else I work with has the same version of Office I have (this is what I get for getting the newest version when everyone else is clinging to the older version they know how to use), but it looks like something that would be useful for such a situation.

 

[zgozvrm]

Actually, this is fairly easy using VBA.

Here's a small example (see attachment) ...

The "Master" tab has 2 blue cells (one for each Employee) at $B$4 and $B$5.
The "Employee 1" tab has a yellow cell for data at $B$5
The "Employee 2" tab has an orange cell for data at $D$6

Change the value in any of these 4 cells, and the appropriate cell on the other tabs will change accordingly.


To view the VBA code, click on Tools -> Macro -> Visual Basic Editor

You should see a project tree (much like Windows Explorer) on the left side of the window. One of the items will be named "VBAProject (Form Test.xls)" Below that, you should see 3 sheets and 1 workbook object. Double-click on each of the sheet objects to see the code for each tab on the workbook.

Note that code written in VBA can contain malicious virus-like code. It is likely that your security settings are set to block macros (VBA code) from running. You'll either have to set your security settings lower (and trust me) or, I can post the code for you.