Formula

[DVM]

May I please get a help on this formula?

Please see the attachment for the description and example of the formula I need.
Best regards
Vilas Desai

[sdckapr]

I don't understand. Your explanation is discussing cells with no information in it. Is your example correct?

Steve

[DVM]

Hi Steve,
Please advise me what part of the description you could not understand. I know it is a bit complex to narrate and also to understand but if you could please advise me from which part you did not follow I would be very happy to explain it to you again.
Best regards
Vilas Desai

[HansV]

The description in column A and the existing data in G4:J24 appear to be unrelated. For example, you state that Ln = I(n-1) + L(n-1), but column I contains letters

[DVM]

Apologies for that, by mistake I deleted two unused cols which caused the error
Here is the correct attachment.

[HansV]

What should happen if the user selects type A in row 5, type B in row 6, type A in row 7, type B in row 8, etc.?

[DVM]

That is a great question. I did think I should mention it earlier, but I thought it may get more complex. So wanted to keep it simple. Here is the answer:Ideally, I would want keep a count of all different types individually. for example,
IF type A appears in Rows 2,7,13,15, 19, 21 then I wouul like to add up all In-1 and Kn-1 upto rows 19 because beyond 19, the sum becomes 11 which exceeds 10 for type A. At this instance the active cell moves on to K21 and error message will pop-up followed by the message to select Jb BX type and enter Start Terminal Number.

[HansV]

If there is more that you have left out to keep it simple, please mention it now. There is no point in creating a solution only to find that it won't work because the actual problem is far more complicated.

[DVM]

Thanks a lot for this kind gesture. If a 0 is encountered in col I, Col L must be blocked for edition. Best Regards, VD

[HansV]

You requested "On answering these questions, $K5 = Ans2 & Ans 1". Wouldn't it be better to store the box type in one column and the box number in another column? That would make it easier to add the numbers for a specific box type. If you need the concatenated values, you can use a formula for that.

[DVM]

Absolutely no problem as long as the two cols are outside of col Z (ex it can be col AA and AB)

[DVM]

And if you giving me a macro please have it placed in the file you have (pw vdesai!) or please advise me where to place it in the masterList wsh macro.

[HansV]

Sorry, I have absolutely no idea how this could be integrated into the worksheet module of the MasterList sheet. It's becoming far too complicated for me.

[DVM]

OK. I understand it. I mentioned "Worksheet module" of MAsterList becuase I ASSUMED that is where the macro would be placed. I have no specific location where it would be placed. If needed I could take this part out of MasterList wsh and place it on another wsh.

[HansV]

Your request is rather complicated. This is the best I can do.

JnBxSample.xlsm

[DVM]

Could you please advise me how do I run the macro or how do I test the program. Best regards Vilas Desai

[HansV]

Double-click a cell in column K, in row 5 or below.

[DVM]

I do see its very complex - but I really appreciate your efforts and thanks a lot for that.

[DVM]

Hello Hans,
I am trying to get some work around, so could you please provide me just this :

When the selected cell is $K5 a msg box pops up :
Q1: Select Jn Box Type
Q2: Enter Jn Box Number
On answering these questions, $K5 = Ans2 & Ans 1
Ex: Type A and IJB will give $K$5 = 1JBA

I am intending to use this in the MasterList! Is it possible? If not then I will use it in another wsh.
Thanks in advance and best regards
Vilas Desai

[HansV]

Do you want a dialog to pop up EACH TIME the user selects a cell in column K? Or when the user double-clicks a cell in column K? Or ...?