Automatically updating cells

[mcech]

Ok so here's whats up.

I have just finished rebuilding an excel workbook that exists to track upcoming and recent activities with potential donors for a non-profit. There are hundreds of names spread out between over a dozen sheets.

What I need is some sort of way (through formulas, links, whatever, I'm desperate) so that a row on one sheet for a contact who is listed on another sheet can automatically be updated when new information is entered without having to track down every row in the workbook that this contact is in.

I thought it would be something simple like c1=c4 or whatever but the problem is that the rows move when we either add new contacts or reorganize their order, so essentially I need to be able to link the information in one cell to another rather than just link the information in that specific location.
Does that make sense?

here's an example.

our executive director, Deb, is often brought along to meetings with potential donors by board members (each of whom has their own sheet). The activities are then recorded into their "recent moves" by saying "Deb and ____ had lunch with ____ and ____" and then they will decide what to do in the future and will put something like "Deb and ___ will meet him/her for coffee on 11/10"
So Deb, on her sheet, needs the rows that have her involved in the moves to be copied in. Then, if we are to go and make any changes, we need those changes to be reflected on the other sheets as well.

I don't know if there is a way to do this with some sort of flagging where something pops up on their sheet that says "rows in sheet __ and sheet ___ contained 'Deb'" or if there's a way to just highlight them so I can find them later (I tried conditionally formatting everything so that cells containing specific names will be highlighted but I had no luck as it would only work for one name rather than the 5 necessary.)

Or maybe this is something that would be better suited for Access or an online cloud thing. I have no idea. All I know is that keeping all of this information straight with all of these people is somewhat similar to herding cats.

please help!

[mikeTRON]

Can you post a sample workbook?

[mcech]

Here is the sample. The entire workbook is SIGNIFICANTLY bigger but I narrowed it down to one staff (Deb) and one board (Penny).

The highlighted section at the bottom of Deb's sheet is what needs to be linked with the highlighted rows on Penny's sheet. I want to be able to change either of the cells and have them be changed on the other sheet. I also need to be able to add or delete rows on either sheet and have the information still be consistent.

MovesManagementFAKE.xlsx

Or if there is a way to create some sort of notification that pops up on Deb's sheet when her name is entered on another person's sheet

[FDibbins]

Hi, welcome to the forum

I want to be able to change either of the cells and have them be changed on the other sheet.

That is not possible with regular formulas A cell can contain either a formula or data, not both.

This looks like the classic mistake of creating what you want the output to look like, 1st, and then trying to fit the data input around it. We generally try and keep all data input on 1 sheet, creating and designing that for ease of use - and then create the output sheets afterwards.

Would it be possible for you to have all your inputs on 1 sheet?

[mcech]

Would it be possible for you to have all your inputs on 1 sheet?

Not really, unfortunately. There are a dozen board members and 5 staff members that all have interactions with different people. The overlap is where I need consistency because with that many moving parts it is so hard to keep track of everyone.

And as for the layout, we adopted it from another company who said it works for them. I am new to the organization and personally believe that there must be some other way that is much easier, however the problem comes from the fact that the information needs to be user friendly or no one will update their activities, so Excel seemed to be somewhat of the best choice.

[mikeTRON]

FDibbins was NOT saying Excel will not work for you. He knows better than most what it is capable of. What he WAS saying is that you need to standardize the data inputs and outputs to eliminate the need for these complex issues that have been created by working in a suboptimal order.

[mcech]

I know, I was saying that Excel may not be the best choice.
and as for standardizing or simplifying, there really isn't any good way to make that happen, unfortunately. I was just hoping there would be some sort of easy solution.

[mikeTRON]

I disagree, you just need to see if you can modify the inputs into a standard template.

Standardizing data is always much easier than having clever solutions. I deal with 20M records in Excel for ONE reporting tool and it is easy because the data is in a format that allows it to be... if that makes sense.

[mcech]

That makes sense so if you were to look at the sample I posted, how would you simplify it?

[FDibbins]

Mike, thanks for the support, and you were right on the money. Excel may still be an option for this, but the structure will need careful thought.

And as for the layout, we adopted it from another company who said it works for them

Can you find out from them how they did that? Maybe get a copy of the template?

[mcech]

All I know is that it was a local Montana organization that made us a template and we filled it out. I tried to track down a copy of the template but no luck. Sorry I can't provide everything, I definitely would if I knew where to find everything.