Need Excel 2003 Programming help...

[Sriram]

Dear Friends,

I know VB programming & also knew that excel macro includes VB codes. But I
don't know how to select cells ad how to check a data in a particular cell
and inputing a data to another cell w.r.f to the checking cell.

I'm using excel 2003.

Example:

A B C D E F G H I J K L M N
O
1 - - - - - - - CA NS N - - - CANS
2 - - - - - - - CA WIP N - - - CAWIP
3 - - - - - - - CA C N - - -
CACMPD
4 - - - - - - - CA C Y - - -
CRRCVD
5 - - - - - - - RO NS N - - - RONS
6 - - - - - - - RO C N - - -
ROCMPD
7 - - - - - - - RO C Y - - - RORDY

We are doing telecom cell site project. Let me explain about the sheet snap
i mentioned above.

H column states the acceptance status of the site. This will have 3 status
viz. C - Cleared, CA - Conditionally Accepted, RO - Reoffered.
I column states the Pending jobs status. This will contain 3 status NS - Not
Started, WIP - Work In Progress, C - Completed.
J column states Ready for Reoffer or Compliance Report received.This will 2
status Yes or No.

Now the scenario is that if the site has been Cleared, then no issues.

If the site is CA, then the pending job has to be compelted and a Complaince
Report to be submitted stating that the pending job are done.

If the site is RO, then pending jobs to be completed and it has to be
reoffered for another visit to client.

If H = C, then no chekcking, direclty putting N = Cleared.
If H = CA and I = NS, then O = CANS, that means site is Conditionally
Accepted and pending job clearance Not Started yet.
If H = CA and I = WIP, then O = CAWIP, means pending jobs clearance Work In
Progress.
If H = CA and I = C, then O=CACMPD, means site is CA and pending jobs
Completd.
If H = CA and I = C and J = Y then o = CRRCVD, means Compliance Report
received.

Same case for H = RO,except the last one if H = RO and I = C and J = Y, then
O = RORDY.

I have done this with nested IF formula but it supports till 7 if conditions
only.

So, kindly anybody help me out by giving some tips how to select the cells
and it s values in a particular sheet or somethign like this.

If you are unable to understand what I want to say then feel free to contact
me on my emil id (e-mail address removed). I'll email you the worksheet itself.

Thanks in advance
Sriram

 

[Ardus Petus]

=IF(OR(I1="NS",I1="WIP"),H1&I1,IF(J1="Y",IF(H1="CA","CRRCVD","RORDY"),H1&"CMPD"))

HTH

 

[Mike Fogleman]

Addressing the 7 nested IF statement limit, there is a work-around that will
give virtually limitless nested IFs. Warning: trying to follow the logic in
these MegaFormulas may lead to insanity. Consider the formula that Ardus
offered you:

=IF(OR(I1="NS",I1="WIP"),H1&I1,IF(J1="Y",IF(H1="CA","CRRCVD","RORDY"),H1&"CMPD"))

There are only 3 nested IFs here, but as you know, you could have up to 4
more. Now you name the formula as you would name a range of cells.
Insert/Name/Define. Put your formula in the Refers To: box, give it a name
at the top like Formula1 and click ADD. Do this for other formulas as well
so you have formula names refering to multi IF statements.
In the worksheet you use the formula names in other formulas. The Name is
considered as only one argument in a cell formula, even though it refers to
many arguments. ie = IF(formula1, formula2, formula3). If each named formula
had 7 nested IFs, then this workbook formula would be evaluating 21 IF
statements. Now re-read the warning at the beginning

Mike F

 

[Sriram]

Dear Mike,

As you suggested me, I've created separate formula for each of my results
and all have come Good without error.

But as you told, I've created Define Names with the mentioned formula and
substitute them as you told like =IF(Formula1, Formula2, Formula3...), but it
is not working.

In L column I've the status like NR, ND, WH, D, WIP, ENP and C.
My first formulae is IF($L4="NR", "NA")
My second formula is IF(OR($L4="ND",$L4="WH",$L4="D",$L4="WIP"),"TNC")

In 4th Row L Column I mentioned "NR"
In 6th Row L Column I mentioend "ND"

As you said after creating these two formula as Define Names with the names
Forma1 and Forma2 respectively and entered the same in a blank column say in
Z column of 4th row as below,
=IF(FORMA1,FORMA1,FORMA2)

But it is showing error as #VALUE.
If the same formulae is entered in 6th row Z column, it is oming Correct.
Because 6th row L column value is ND & giving result as TNC. This is correct.
And 4th row L column value is NR, and it should show NA, insted it is
showing #VALUE.

Kindly help me out, if I've done any wrong in this.

Thanks in Advance.

Sriram

 

[Mike Fogleman]

You have done good so far, but you still need to qualify the IF statement in
the cell. The first argument always requires some kind of comparison like =,
<>, <, >, etc. So just add a comparison to your first argument. In your case
it would be:

=IF(Forma1="NA",Forma1,Forma2)

Mike F

 

[Sriram]

Dear Mike,

Thanks for your below help for my earlier problem. Now I've come up with
another problem, can you please solve it for me.

My self and 3 of my other colleagues are working in a Project Management
Team with different roles. We use to track our work flow in the manner of
Trackers separately. But all of our 4 data will contain around 50% of same
details. One of our 4 members is the ultimate MIS updater and we other 3 use
to collect the relevant common data once or twice a week and update with our
records. It reflects a little time waste.

So I've planned to create a Master file, so that our MIS after updating his
file daily, the common data will be reflected in our rest 3 memeber's files.
I did this. Even though I've planned to give some Password protection to the
respective team member's sheets.

For example let our Master file will be containing 10 sheets.
1st sheet will be containing our 4 names.
2nd is of the ultimate MIS executive's.
3rd and 4th will be of mine.
5-7 will be of the next one.
8-10 is for the last member.

Now my question is that, the Master file will be available in our server and
at any time anybody can open this. While opened only the First sheet should
be Visible rest all should be Hidden/Invisible. Whenever the respective
person clicks his name, a password should be asked and after entering his
password, then his respective sheets should be visible and he can make his
update. After updating he can normally close the file. The same case for rest
also.

Kindly tell me option how to accomplish this.

My quetion is to Show / Make Visible one or more sheets by giving password
and that too by clicking the respective names/links.

Thanks and Regards,
Sriram Subramaniyan