How to linked a certain cell when criteria is meet

[ECYOJ]

Hi. Please help me. Heres my issue.

I got two sheet the GRAPH and the DATA.

I needed to get the value of a cell(No. of Days) in the sheet DATA then copy it to the Sheet Graph.

When the Date Submitted was not yet put the No. Of days must be copied under PENDING in the Sheet Graph. But if the date submitted was already put the No. Of days must be transferred to SUBMITTED.

I attach the file for your reference

Please help Thanks.

[raja_puligadda]

Please check the data once. In data sheet.. for each SI, 3 row names under vessel found (PWA, HCE, CGA) which are same for all SI. But in graph data Different different names found under SI..(LHA,IBI..)

[newdoverman]

Just taking a guess that you want the data from column F if there is or is not a date in column D of the Data worksheet.

Enter in Graph!B32 and copy across.

Please Login or Register  to view this content.

Enter in Graph!B33 and copy across.

Please Login or Register  to view this content.

[ECYOJ]

Just taking a guess that you want the data from column F if there is or is not a date in column D of the Data worksheet.

Enter in Graph!B32 and copy across.

Please Login or Register  to view this content.

Enter in Graph!B33 and copy across.

Please Login or Register  to view this content.

Hi. Thank You for your formula. But it does'nt return the value of the cell that i want although it automatically transferred to submitted when the date was inputted. I attach the file. Thank You

[ECYOJ]

Just taking a guess that you want the data from column F if there is or is not a date in column D of the Data worksheet.

Enter in Graph!B32 and copy across.

Please Login or Register  to view this content.

Enter in Graph!B33 and copy across.

Please Login or Register  to view this content.

Hi. Thank You for your formula. But it does'nt return the value of the cell that i want although it automatically transferred to submitted when the date was inputted. I attach the file. Thank You

[newdoverman]

This is a much different data setup than the original. Will have to take a look at it tomorrow. Merged cells are a real curse to work with. Will likely have to unmerge all of them to get a solution worked out....if possible.

[FDibbins]

As Ron says, merged cells are the devils work, you should avoid them if at all possible.

I have come up with a suggestion, using a helper column on Data and a helper row in Graph

1. In Data G2, copied down, use this...
=IF(A2="",G1,A2)
2. in Graph B29, copied across, use this...
=IF(B30="",A29,B30)
Both of these can be hidden if you want.

Then for thePending...
=IF(COUNTIFS(Data!$G$2:$G$62,Graph!B$29,Data!$B$2:$B$62,Graph!B$31,Data!$D$2:$D$62,"<>")=0,"",INDEX(Data!$F$2:$F$62,MATCH(B29&B31,INDEX(Data!$G$2:$G$62&Data!$B$2:$B$62,0),0)))
copied across
for Submitted...
=IF(COUNTIFS(Data!$G$2:$G$62,Graph!B$29,Data!$B$2:$B$62,Graph!B$31,Data!$D$2:$D$62,"<>")=0,INDEX(Data!$F$2:$F$62,MATCH(B29&B31,INDEX(Data!$G$2:$G$62&Data!$B$2:$B$62,0),0)),"")
copied across
(Hope I got those 2 the right way round)

[ECYOJ]

Hi. I try your suggestion but for the item with same name and same code example the name barican has 2 PWA it copied only the data Data:F7 but for the PWA Data;F8 it didnt copy. And also is there any way that the value will not be shown in the graph. Thank You

[ECYOJ]

Thank You i will wait for your reply.

[FDibbins]

So, do you want the values added?

[ECYOJ]

So, do you want the values added?

Not added actually but only copied.

[ECYOJ]

As Ron says, merged cells are the devils work, you should avoid them if at all possible.

I have come up with a suggestion, using a helper column on Data and a helper row in Graph

1. In Data G2, copied down, use this...
=IF(A2="",G1,A2)
2. in Graph B29, copied across, use this...
=IF(B30="",A29,B30)
Both of these can be hidden if you want.

Then for thePending...
=IF(COUNTIFS(Data!$G$2:$G$62,Graph!B$29,Data!$B$2:$B$62,Graph!B$31,Data!$D$2:$D$62,"<>")=0,"",INDEX(Data!$F$2:$F$62,MATCH(B29&B31,INDEX(Data!$G$2:$G$62&Data!$B$2:$B$62,0),0)))
copied across
for Submitted...
=IF(COUNTIFS(Data!$G$2:$G$62,Graph!B$29,Data!$B$2:$B$62,Graph!B$31,Data!$D$2:$D$62,"<>")=0,INDEX(Data!$F$2:$F$62,MATCH(B29&B31,INDEX(Data!$G$2:$G$62&Data!$B$2:$B$62,0),0)),"")
copied across
(Hope I got those 2 the right way round)

The values that were copied for the CODE are the same e.g GEC i got 3 GEC the values that were copied to the Graph are all 15 same goes for PWA,KBI Etc. I guess if they have the same code that values that was being copied was the same as the first code that matches.

[FDibbins]

OK try the attached, I had to add an extra helper column and row

[newdoverman]

Maybe this will work for you.

[ECYOJ]

OK try the attached, I had to add an extra helper column and row

Thanks for the formula. It works. But for the KKN and SJY it shows N/A

[ECYOJ]

Thanks for the formula. It works. But for the KKN and SJY it shows N/A

And also can you explain to me how did you do it so that i can use it when i added new data. Thanks.

[ECYOJ]

Thanks for the formula. It works. But for the KKN and SJY it shows N/A

And also can you explain to me how did you do it so that i can use it when i added new data. Thanks.

[FDibbins]

You are missing an entry for Penaranda on Graph sheet
You only have 1 GRO on Graph, but 2 on Data. Fix that and it should work

[ECYOJ]

You are missing an entry for Penaranda on Graph sheet
You only have 1 GRO on Graph, but 2 on Data. Fix that and it should work

Thank You. It works well now.

[FDibbins]

Im happy we got it resolved for you, even though it was a bit messy, and thanks for the feedback