Excel program with multiple tabs with shared information linked

In summary, Malinko is asking about how to start a formula in Excel that will change the values in cells in a linked table when the values in those cells are changed in another cell in the same linked table. He also asks about whether or not the employees need their own sheets, or if they can modify a master sheet.
  • #1
2
0
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?
 
Computer science news on Phys.org
  • #2
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.
 
  • #3
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.
 
  • #4
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.
 
  • #5
Type an equals sign, then click on the cell you want to reference. That's all there is to it.
 
  • #6
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.
 
  • #7


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.
 

Attachments

  • Form Test.xls
    30 KB · Views: 276

1. What is the purpose of using multiple tabs in an Excel program?

The purpose of using multiple tabs in an Excel program is to organize and categorize different types of data or information. This allows for easier navigation and access to specific information, as well as a more efficient way to view and analyze data.

2. How can information be shared between multiple tabs in an Excel program?

Information can be shared between multiple tabs in an Excel program by using formulas or linking cells. This allows for the information to be automatically updated and synced between tabs, eliminating the need to manually input data multiple times.

3. What are the benefits of using linked information in an Excel program?

Using linked information in an Excel program can save time and reduce errors, as the information is automatically updated and synced between tabs. It also allows for a more organized and cohesive dataset, making it easier to analyze and interpret the data.

4. Can multiple users access and edit the same information on different tabs in an Excel program?

Yes, multiple users can access and edit the same information on different tabs in an Excel program as long as the file is saved in a shared location, such as a shared drive or cloud storage. This allows for collaboration and real-time updates to the linked information.

5. Are there any limitations to using multiple tabs with shared information in an Excel program?

While there are many benefits to using multiple tabs with shared information in an Excel program, there are some limitations to consider. These may include potential data loss if the linked information is accidentally deleted or overwritten, as well as slower performance if there are a large number of tabs and linked data. It is important to carefully plan and manage the use of multiple tabs in order to avoid these limitations.

Suggested for: Excel program with multiple tabs with shared information linked

Replies
7
Views
668
Replies
1
Views
286
Replies
3
Views
2K
Replies
20
Views
453
Replies
1
Views
905
Replies
16
Views
2K
Replies
4
Views
1K
Replies
3
Views
808
Back
Top