CODEWITHSUNDEEP
excelexcel-formula
Xavi Gómez Canals
Xavi Gómez Canals

Reputation: 155

Find value in column in another sheet - subject to conditions

I have an Excel workbook with a lot of worksheets. In one sheet (INDEX) I need a column with "si" or "no" values (or true/false,...boolean). The value decided by other sheets that contain columns filled with strings like 0gr, or 10gr, or 1kg,... These values are weights of a products.

I need a value TRUE (or "si") in INDEX sheet when, in another sheet all values are 0gr (or 0).

Sample:

Sheet INDEX
abc------TRUE
bcd------FALSE
cde------TRUE
...

Sheet abc             Sheet bcd              Sheet cde
1-------0gr          1--------0gr          1------------0gr
2-------0gr          2-------100gr         2------------0gr
3-------0gr          3--------0gr          3------------0
4-------0gr          4--------1kg          4------------0gr

enter image description here

Upvotes: 0

Views: 108

Answers (3)

Shawn Mehan
Shawn Mehan

Reputation: 4578

You need something like this in the target cells in Book Index

=IF([abc]'Adjument'!$G$6=0,"YES","NO")

where you will replace Adjument with the sheet name in Book abc, and column and rows as appropriate.

In general, the format is

 =IF([WORKBOOKNAME]'SHEETNAME'!$A!1=VALUE,"TRUESTRING","NOTTRUESTRING")

where, you may replace everything that is an uppercase character of the 1, except the initial IF.

Upvotes: 0

ManishChristian
ManishChristian

Reputation: 3784

Put this formula next to your cells abc, bcd, cde.
Assuming your WorkBook name is abc.xlsx, bcd.xlsx and cde.xlsx. Please change it as per your need. you will need to change the sheet names as well.

For abc:

=IF(OR( (COUNTIF([abc.xlsx]Sheet4!$B:$B,"0gr")=MATCH("",[abc.xlsx]Sheet4!$B:$B,-1)-1), (COUNTIF([abc.xlsx]Sheet4!$B:$B,0)=MATCH("",[abc.xlsx]Sheet4!$B:$B,-1)-1), (COUNTIF([abc.xlsx]Sheet4!$B:$B,"0gr")+COUNTIF([abc.xlsx]Sheet4!$B:$B,0)=MATCH("*",[abc.xlsx]Sheet4!$B:$B,-1)-1)), "YES","NO")

for bcd:

=IF(OR( (COUNTIF([bcd.xlsx]Sheet5!$B:$B,"0gr")=MATCH("",[bcd.xlsx]Sheet5!$B:$B,-1)-1), (COUNTIF([bcd.xlsx]Sheet5!$B:$B,0)=MATCH("",[bcd.xlsx]Sheet5!$B:$B,-1)-1), (COUNTIF([bcd.xlsx]Sheet5!$B:$B,"0gr")+COUNTIF([bcd.xlsx]Sheet5!$B:$B,0)=MATCH("*",[bcd.xlsx]Sheet5!$B:$B,-1)-1)), "YES","NO")

For cde:

=IF(OR( (COUNTIF([cde.xlsx]Sheet6!$B:$B,"0gr")=MATCH("",[cde.xlsx]Sheet4!$B:$B,-1)-1), (COUNTIF([cde.xlsx]Sheet6!$B:$B,0)=MATCH("",[cde.xlsx]Sheet6!$B:$B,-1)-1), (COUNTIF([cde.xlsx]Sheet6!$B:$B,"0gr")+COUNTIF([cde.xlsx]Sheet6!$B:$B,0)=MATCH("*",[cde.xlsx]Sheet6!$B:$B,-1)-1)), "YES","NO")

Upvotes: 0

pnuts
pnuts

Reputation: 59495

I think something like this might work: 

=IF(AND(COUNTIF('Ajuntament Edifici Pral.'!G:G,"*gr")>0,COUNTIF('Ajuntament Edifici Pral.'!G:G,"*gr")>COUNTIF('Ajuntament Edifici Pral.'!G:G,"0gr")),"si","no")

for the cell you indicate. However if only the date changes, it would give the same result for all other cells in the same column, even if copied up/down.

Upvotes: 1

Related Questions