Assuming your have done VLOOKUP correctly, #N/A only means that particular activity is not open-ended. Delete all rows containing #N/A leaving only those with NP and NS. Re-sort "ACT" to remove those blank rows in between. Save it and import it back to your P3 schedule.
In case you still experiencing any problem, then give me your email address and i will send you an excel file to give you an example.
Using Vlook up function is a BIg problem. If you can exlain in greater detail then it would be great. I get "N/A#" error for activities that do not contain NP or NS and I really do not know whats to be done with activities having both.
Thanks
Milind
Member for
24 years 3 months
Member for24 years4 months
Submitted by Jaime Linchangco on Fri, 2006-06-23 20:36
There another way using Excel. Its a little bit tricky and need few knowledge in excel "Function" command. Try the following steps.
1). Create new activity code OPEN (Lenght=2) with values of "NP" for No Predecessor and "NS" for No Successor.
2). Export all with Activity ID and Activity Code as "open.dbf". I always preferred data base.
3). Under Tool, Go to Schedule and click "Open-ended" then Run Schedule. View it then highlight all of the open-ended activities and "Copy" it (Ctrl + C).
4). Open a new excel file and Paste it (Ctrl + V) in Column A.
5). Highlight again all those open-ended and go to Data, then Filter, then click "Autofilter".
6). In the top most cell, you will see the drop down arrow and go to "Custom". Use "Contain" "No Predecessor". It will filtered all activities without predecessors. Copy it again (Ctrl + C) and paste it on sheet 2.
7). On worksheet 2, on the right side (Column B) of those activities without predecessors, use the formula "MID".
=MID(A2,11,10)
Copy this formula down to the last activities.
There you will see now the activity ID only. Copy it as a "Value" to remove the formula.
8). Beside the activity ID, type "NP" for No predecessor (on Column C).
9). For no Successor, do the same step 6 and 8. Do it in another worksheet (say Sheet 3) Except the MID formula would be now,
=MID(A2,52,10)
Then, type "NS" (Colum C) for No Successor.
9). Now, open the exported file "open.dbf" that you created inside P3 with Activity ID and Activity Codes. Note: Both files are now open.
10). Use function "VLOOKUP" to assign the proper code under "OPEN" at the exported file.
11). Once done, save "open.dbf" and close excel.
12). Inside P3, import the file "open.dbf".
13). You could now filter all open-ended activities.
With little practice, these process would be very helpful especially when you have hundreds or thousands of open-ended activities.
Member for
24 years 3 monthsRE: Filter Open ended Activities??
Hi Milind,
Good Day!
Assuming your have done VLOOKUP correctly, #N/A only means that particular activity is not open-ended. Delete all rows containing #N/A leaving only those with NP and NS. Re-sort "ACT" to remove those blank rows in between. Save it and import it back to your P3 schedule.
In case you still experiencing any problem, then give me your email address and i will send you an excel file to give you an example.
Cheers!
Jaime
Member for
19 years 4 monthsRE: Filter Open ended Activities??
Jamie
Using Vlook up function is a BIg problem. If you can exlain in greater detail then it would be great. I get "N/A#" error for activities that do not contain NP or NS and I really do not know whats to be done with activities having both.
Thanks
Milind
Member for
24 years 3 monthsRE: Filter Open ended Activities??
Hi Millind,
There another way using Excel. Its a little bit tricky and need few knowledge in excel "Function" command. Try the following steps.
1). Create new activity code OPEN (Lenght=2) with values of "NP" for No Predecessor and "NS" for No Successor.
2). Export all with Activity ID and Activity Code as "open.dbf". I always preferred data base.
3). Under Tool, Go to Schedule and click "Open-ended" then Run Schedule. View it then highlight all of the open-ended activities and "Copy" it (Ctrl + C).
4). Open a new excel file and Paste it (Ctrl + V) in Column A.
5). Highlight again all those open-ended and go to Data, then Filter, then click "Autofilter".
6). In the top most cell, you will see the drop down arrow and go to "Custom". Use "Contain" "No Predecessor". It will filtered all activities without predecessors. Copy it again (Ctrl + C) and paste it on sheet 2.
7). On worksheet 2, on the right side (Column B) of those activities without predecessors, use the formula "MID".
=MID(A2,11,10)
Copy this formula down to the last activities.
There you will see now the activity ID only. Copy it as a "Value" to remove the formula.
8). Beside the activity ID, type "NP" for No predecessor (on Column C).
9). For no Successor, do the same step 6 and 8. Do it in another worksheet (say Sheet 3) Except the MID formula would be now,
=MID(A2,52,10)
Then, type "NS" (Colum C) for No Successor.
9). Now, open the exported file "open.dbf" that you created inside P3 with Activity ID and Activity Codes. Note: Both files are now open.
10). Use function "VLOOKUP" to assign the proper code under "OPEN" at the exported file.
11). Once done, save "open.dbf" and close excel.
12). Inside P3, import the file "open.dbf".
13). You could now filter all open-ended activities.
With little practice, these process would be very helpful especially when you have hundreds or thousands of open-ended activities.
Goodluck!
Jaime