Change value of a cell based from another cell in a different sheet

[Kelsinathaleen]

Hi, I hope someone could help me with a tracker.

There is sheet1 and sheet2
In sheet 1, there is a reference number in column 1 (1, 2, 3, and so on) which I use an identifier of each item on that sheet for sheet2
I wanted to put a formula for sheet 1 that says whether "pending" or "ready" based from a column in sheet 2
If sheet2 Status says "not yet solved", sheet1 status should be "Pending"
If sheet2 Status, for example for item #1, don't have any "Pending" then sheet1 status should be "Ready"

Each item on sheet1 can only be used once unlike for sheet2 where there can be a lot of queries per item

Hope this clarifies

Sheet1:

Sheet2:

 

[jtakw]

Hi,

See if this works for you:

=IF(COUNTIFS(Sheet2!A2:A10,A2,Sheet2!C2:C10,"Not yet solved"),"Pending","Ready")

 

[Kelsinathaleen]

Hi,

See if this works for you:

=IF(COUNTIFS(Sheet2!A2:A10,A2,Sheet2!C2:C10,"Not yet solved"),"Pending","Ready")

Hi, thanks for attending to my concern. I think that it didn't work. All the items became ready while the sheet2 has pendings

 

[jtakw]

Have you adjusted the formula cell references according to your data?
And, you want "Pending", if Sheet2 Column C says "Not yet solved" for the corresponding Item # in Column A, correct?
If so, how does "Not yet solved" get entered, manually, or by formula?

 

[Kelsinathaleen]

Have you adjusted the formula cell references according to your data?
And, you want "Pending", if Sheet2 Column C says "Not yet solved" for the corresponding Item # in Column A, correct?
If so, how does "Not yet solved" get entered, manually, or by formula?

Yes, I did adjust, and that's correct for your second statement.
The "Not yet solved" and "Solved" status in Sheet2 is manually entered

 

[jtakw]

Oops my fault, forgot Absolute referencing, it should work now:

=IF(COUNTIFS(Sheet2!A$2:A$10,A2,Sheet2!C$2:C$10,"Not yet solved"),"Pending","Ready")

 

[Kelsinathaleen]

Oops my fault, forgot Absolute referencing, it should work now:

=IF(COUNTIFS(Sheet2!A$2:A$10,A2,Sheet2!C$2:C$10,"Not yet solved"),"Pending","Ready")

Oh my, I tried to to add more items and there had been an error:

Here is Sheet2:

 

[jtakw]

BOTH ranges within the formula needs to be the same size.

You have:
=IF(COUNTIFS(Sheet2!A$2:A$10,A2,Sheet2!C$2:C$18,"Not yet solved"),"Pending","Ready")

Which won't work, thus #VALUE

If you need to increase the range, increase Both accordingly, do something like this:

=IF(COUNTIFS(Sheet2!A$2:A$100,A2,Sheet2!C$2:C$100,"Not yet solved"),"Pending","Ready")