Yet another Excel question.

[Dave]

Yes, my Excel is still goofing up on that one sheet. No answer for it. I'm just working around it. But now I have a very particular problem that I'm wondering if I'm just missing a simple solution.

I have a sheet with 4 tabs - Kevin Projects, Dale Projects, Dave Projects, and Brian Projects. Each of these is broken down in months with several informational columns like start date, etc. What I'd LIKE to do is, if someone adds something to their individual sheet, have it dynamically update on a Team Projects tab. Level of difficulty: I'd like to be able to do this without using VBA.

Ideas?

 

[MindDetective]

I suppose you want it to update seamlessly, meaning no blank rows, and maintain chronological order, meaning the entries from each tab will be woven together?

 

[Dave]

If possible. I know there's an "importrange" command, but I think that's not until Excel 365 as it's not working for my Excel 2013.

 

[MindDetective]

OK, I have a crazy idea but haven't tried it out, so I have no formula to post in here:

1.) Paste the very first entry (by start date) onto your Team Projects tab. You have to start somewhere. If this is something you cannot do, you can make a false row with false start date that precedes any of the projects. In either case, you have a starter row to build off of.

2.) Next you need to do a competition of the other four tabs using nested IF statements to figure out which has the next earliest start date. So, in row 2 on the projects tab you need to use row 1's starting date, then find the next date after that on Kevin's, Dale's, Dave's, and Brian's tabs and compare each one to the others. ex.: If Kevin's is earlier than Dale's, then if Kevin's earlier than Dave's, then if Kevin's is earlier than Brian's then add that row, else if Dale's is earlier than Dave's, then... etc. That will be a long-ass nested IF statement!

3.) After you set up row 2, though, then all the other rows should just be a copy/paste of that row, except referencing the row above it (row 3 references row 2, etc.)

Will it work? *shrug* It should, I think. You know enough about excel to try it, I suspect.

 

[Dave]

Hmm. Okay. I'll give that a go.

 

[strawman]

OK, I have a crazy idea but haven't tried it out, so I have no formula to post in here:

1.) Paste the very first entry (by start date) onto your Team Projects tab. You have to start somewhere. If this is something you cannot do, you can make a false row with false start date that precedes any of the projects. In either case, you have a starter row to build off of.

2.) Next you need to do a competition of the other four tabs using nested IF statements to figure out which has the next earliest start date. So, in row 2 on the projects tab you need to use row 1's starting date, then find the next date after that on Kevin's, Dale's, Dave's, and Brian's tabs and compare each one to the others. ex.: If Kevin's is earlier than Dale's, then if Kevin's earlier than Dave's, then if Kevin's is earlier than Brian's then add that row, else if Dale's is earlier than Dave's, then... etc. That will be a long-ass nested IF statement!

3.) After you set up row 2, though, then all the other rows should just be a copy/paste of that row, except referencing the row above it (row 3 references row 2, etc.)

Will it work? *shrug* It should, I think. You know enough about excel to try it, I suspect.

I'm having a hard time understanding #2. If you just copy and paste references, then on the next line down it'll only compare items on that line for the other worksheets. If you're doing a search then I suppose you perform the lower bound by the previous line's date, find the lowest date in each worksheet, then compare those dates.

Sounds painful, but I could see it maybe working with a search.

 

[Dave]

I found another way, which is an addon. But it cost $39 and I don't know if my boss would buy it. Of course, this is all because they won't bother using Project as it would require three more licenses...

And stienman is completely correct - this won't work as stated. I can get it to add sort of, but it misses too much. I may have to do VBA anyway.

 

[strawman]

Why are you trying to avoid vba? It's a pretty powerful tool, handy to have when you need it.

I suspect you could do it simply by treating them each as a database and use a single SQL statement to combine and sort them.

 

[Dave]

Why are you trying to avoid vba? It's a pretty powerful tool, handy to have when you need it.

I suspect you could do it simply by treating them each as a database and use a single SQL statement to combine and sort them.

Because it's going to be used by three other people and I can't lock it down. Doing it without VBA is easier to fix if someone tinkers.

 

[strawman]

Too bad you don't have Access. It wouldn't be hard to create a project management application using it that allows people to add, edit, sort, view, etc the info you're looking at.

There are free MS Project replacements that might be with looking into.

Let's see. If I had to do what you're trying to do...

I'd go ahead and create two new sheets. On the first sheet I'd just set aside hundreds of rows for each person and reference their sheets row by row, essentially copying all their data from individual sheets into one big sheet.

In the second new sheet I'd use a lookup function that should allow you to sort the first sheet before pulling out a specific row. I'm not sure that function exists, though but if it doesn't then I don't think there's a way to do it in cells formulas. Then in that second sheet I'd just pull out each row one by one.

Sounds like a difficult problem, though. Probably something that can be done by hand once a week rather than trying to automate it.

 

[Dave]

We have Access. They just don't want to us it.

 

[MindDetective]

I'm having a hard time understanding #2. If you just copy and paste references, then on the next line down it'll only compare items on that line for the other worksheets. If you're doing a search then I suppose you perform the lower bound by the previous line's date, find the lowest date in each worksheet, then compare those dates.

Sounds painful, but I could see it maybe working with a search.

I meant just doing a lookup using the referenced date. You can put in whole columns for lookup tables.[DOUBLEPOST=1473731730,1473731655][/DOUBLEPOST]I can try and create it but not until tomorrow, I think.

 

[Chad Sexington]

We have Access. They just don't want to us it.

Idiots.

 

[Bubble181]

getting all the info in there is easy. Getting it sorted (and blank lines cut) is what I can't see happening automatically without VBA...though I'm sure it's possible, I just never do it