Need formulas to reflect in the summary sheet.

[chitti babu]

Kindly help,

In Report sheet in column P where there is "1001-
Duplicate incident" and in column V there should be a
external ticket number for the "1001-Duplicate incident" example like IN1319600/SR1312484. IF the condition satisfies then in column AA it should show Valid or Invalid only for those ticket numbers.

For all Invalid status in column X to AA it should refelect in summary sheet with status,reference number and the group name
Example is in the summary sheet

 

[Nebu]

Hi:

You requirement a bit confusing, here is what I got from your query.

• On the Report sheet in column P where there is "1001-Duplicate incident"- this is fine and starlight forward.
• Column V there should be a external ticket number for the "1001-Duplicate incident" example like IN1319600/SR1312484.- Yes you have external ticket numbers here and there are duplicates in this column and it is not exactly like IN1319600/SR1312484, but IN1319600. Do you want to keep the duplicates as well?( You will have to clarify this part).
• IF the condition satisfies then in column AA it should show Valid or Invalid only for those ticket numbers. I am completely lost here on what basis you want categorize it as valid or invalid?
• For all Invalid status in column X to AA it should refelect in summary sheet- I assume that if you have status as invalid from column X thru AA you want it be summarized on Summary tab.

Thanks

 

[chitti babu]

Hi:

You requirement a bit confusing, here is what I got from your query.

• On the Report sheet in column P where there is "1001-Duplicate incident"- this is fine and starlight forward.
• Column V there should be a external ticket number for the "1001-Duplicate incident" example like IN1319600/SR1312484.- Yes you have external ticket numbers here and there are duplicates in this column and it is not exactly like IN1319600/SR1312484, but IN1319600. Do you want to keep the duplicates as well?( You will have to clarify this part).
• IF the condition satisfies then in column AA it should show Valid or Invalid only for those ticket numbers. I am completely lost here on what basis you want categorize it as valid or invalid?
• For all Invalid status in column X to AA it should refelect in summary sheet- I assume that if you have status as invalid from column X thru AA you want it be summarized on Summary tab.

Thanks

Thank you for the communication,

IF there is a "1001-Duplicate incident" in column P then there should be a ticket number in column V (where the ticket number can be IN1319600 or SR1312484). IF "1001-Duplicate incident" has a ticket number then give the status as valid or Invalid in column AA. Keep the duplicates.

You are right, if you have status as invalid from column X thru AA you want it be summarized on Summary tab with the the ref number and group name.

 

[Nebu]

Hi:

Is the sample data the true representation of your original data. As per your logic none of the fields can be pulled to the summary tab from this data set. I am also not sure about the column headings on your summary tab, you have Ref number and Group name repeated multiple times on your Summary sheet. You will have to be really clear abt the file and the fields for us to give a solution. Can you populate the expected results manually and explain. Understand we are not experts in your business process.

Thanks

 

[chitti babu]

Thank you for your communication,

Kindly igonre the summary sheet will let you know about it.

Can you please give me a formula for the below
IF there is a "1001-Duplicate incident" in column P then there should be a ticket number in column V (where the ticket number can be IN1319600 or SR1312484). IF "1001-Duplicate incident" has a ticket number then give the status as valid or Invalid in column AA. Keep the duplicates.

 

[Nebu]

Hi:

Use the following formula in the column AA

=IF(AND(P2="1001-Duplicate incident",NOT(ISBLANK(V2))),"Valid","Invalid")

Thanks