PDA

View Full Version : Solved: Lookup ?? Problem!



lui_roc
02-17-2006, 03:06 PM
I have officially arrived at a dead end. I know what I want to do, but for one, I don?t know if it?s possible, and for two, even if it is have no idea how to do! I would much appreciate some help on the matter.



To make it easy for me to explain I have uploaded the workbook to savefile.com

http://www.savefile.com/files/3037321 (http://www.savefile.com/files/3037321)



Referring to the worksheet called ProjectRegister - cell A8, I would like to select a risk source from a validation list. The validation list would use column B in the RiskSource as it source. Once a risk source had been select it would insert a number of rows depending on how many risk were associated to that source. For example, if railway (which has three associated risks) was select as the risk source, then two new rows would be inserted and the existing row in row 8 would act as the third. The rows that were inserted would copy the same formulas and validation as row 8 (I have created some similar code for this on the Add Row command button).



The following risk information will be automatically entered in the ProjectRegister from the RiskSource:

Risk Title (ProjectRegister column D) to Risk Title (RiskSource column C)
Risk Description (ProjectRegister column E) to Risk Description (RiskSource column D)
Action Description (ProjectRegister column N) to Action Description (RiskSource column F)


Other Possible useful information:

There will be hundreds of Risk Source Records
The user can enter a unique risk, which has no source ID. In this scenario the user will select custom in Risk Source filed and enter risk manually.


I would much appreciate any help on this problem.



Lui

mdmackillop
02-17-2006, 03:28 PM
Hi Lui,
Welcome to VBAX.
I've attached the sample file to your post. You can do this by selecting Go Advanced and Manage Attachments.
Regards
MD

mdmackillop
02-17-2006, 03:39 PM
Hi Lui,
Just to make things completely clear, can you create a Project Register sheet which shows two Risk Source items as you'd like it to appear.

mdmackillop
02-17-2006, 05:52 PM
I've taken an initial stab at this. Let me know if there are any bugs.

lui_roc
02-18-2006, 01:52 AM
MD - thanks for the warm welcome and all your help so far.

Your version of the worksheet is exactly what I was after.



There is a few issues I'm not sure about, they area as follows:


How would I change the formulas in the original row? It seems I can't view them until I add a row?
As soon as I open the workbook a pop-up message appear telling me "this workbook contains links to other data sources ..." Also when I select a risk source a file directory pop-up appears asking me to update values. Is this necessary?
Lastly when I select the blank (custom) in the risk source list it still adds three risks in the other fields?
Once again I appreciate you time and effort



Lui

lui_roc
02-18-2006, 02:15 AM
MD

Please ignore my first two queries, I've managed to work them out. I just didn't look hard enough.

My last query regarding the custom field still stands.

Also I have one other small issue. Is it possible to create the list in the RM sheet automatically from the RiskSource Worksheet?

thanks

Lui

mdmackillop
02-18-2006, 01:24 PM
Hi Lui,
I posted my reply as far as I had progressed it, and hadn't reached custom!
I added some code to extend the list on the RM sheet whenever new items are added. I put it on a new sheet for clarity, the list can be stored on an existing sheet if preferred. I'll look into Custom shortly.

lui_roc
02-18-2006, 02:15 PM
thanks

mdmackillop
02-18-2006, 06:26 PM
Custom incorporated. Select Custom from the list; there is a prompt for a name and a number of rows. The RS list should now be sorted alphabetically, but for Custom which will head the list.

lui_roc
02-19-2006, 12:00 PM
MD, what can I say - it works perfect!! :bow:

I can't thank you enough for all your time and support on this problem.

Once again many thanks

Lui

mdmackillop
02-19-2006, 01:21 PM
Glad to be of help.

lui_roc
03-04-2006, 10:58 AM
Thanks for the advice

I would like a formula to total the risk impact columns (cost, time, safety, quality and impact quotient), which are only negative values, based on the likelihood.

In the likelihood column value:
1 = Rare
2 = Unlikely
3 = Possible
4 = Likely
5 = Almost Certain

For example, the formula for cell G22 on the project register sheet will sum every negative cost value (column G) that has a likelihood (column F) of 1.

I hope that makes sense

Lui