if statement help

[jruschak]

I'm using the formula below to populate a cell if another cell does not contain any of the following "R", "0" or is Blank. if the cell "sheet2!G11" has any of those in it the formula cell is left Blank. If the cell contains anything else then it populates with what is in cell "sheet2!D11"

=IF(LOOKUP($G$1,audit1,sheet2!G11)={"","R",""},"",sheet2!D11

 

[etaf]

try

=IF( OR(Sheet2!G11={0,"","R"}), "", Sheet2!D11)

 

[jruschak]

try

=IF( OR(Sheet2!G11={0,"","R"}), "", Sheet2!D11)

"Thanks for the rely but it did not work. Let me explain the formula, I have two spreadsheets 1 is a work schedule, that list employees and all days of the month. The other spreadsheet list who is working on an exact day. The user selects the day of the month from on sheet and the formula goes to Sheet2 and finds that day and looks to see if there is an "R", "0", or is blank. if yes the person is not working and the formula returns a Blank. If any other data is in the cell then the person is working and it returns the employees name. There may be a better way but I don't know of how. Please helps, seems simple for all the forum experts. "

 

[mumps]

Try: =IF(OR(Sheet2!G11="R",Sheet2!G11="0",Sheet2!G11=""),"",Sheet2!D11)

 

[jasonb75]

Trying to fit your formula to your description,

What is the relevance of $G$1?

What is the range covered by "audit1"?

 

[jruschak]

Trying to fit your formula to your description,

What is the relevance of $G$1?

What is the range covered by "audit1"?

I select the day of the month from a drop down list located in $G$1

"audit1" is the range on the 30 day schedule in sheet2

The formula should select everyone working on that day and populate a daily work form.

if the cells on the work schedule have "R" "0" or is Blank then the person is not working, and should not populate the form. if cells have any other data the person is working and should populate the work form

Thanks or your efforts....

 

[jruschak]

I have tried this formula also, but it didn't work.....

=IF(LOOKUP($G$1,audit1,sheet2!E12)="","",sheet2!D12)&IF(LOOKUP($G$1,audit1,sheet2!E12)="R","",)&IF(LOOKUP($G$1,audit1,sheet2!E12)="0","",)

 

[jruschak]

I tried your formula above that you posted.... =IF(OR(Sheet2!G11="R",Sheet2!G11="0",Sheet2!G11=""),"",Sheet2!D11)

It returned a blank but the employee has an "X" in Sheet2!G11, means she s working and should return the data in cell Sheet2!D11

 

[etaf]

no that will not work - nor will a lookup - need to extract all the matching info - not sure the best way to do that , and may need vba - i'm sure someone with more knowledge will know and answer

 

[jasonb75]

I think I get what you're asking for, but to clarify a few things.

Column D has names, column G has the flag {"",0,"R"}

Formula to return all names in column D where the corresponding entry in column G is not {"",0,"R"}

How does the date selected in G1 relate to the data in sheet2? Would different dates refer to different columns in Sheet2?

What range does "audit1" cover? i.e. A1:Z99

Please give a brief description of the data in that range, how it is laid out, etc, anything that can help us visualise your sheet.

A screen dump of a copy of your sheet, using 4 or 5 fictional people and 1 week of dates showing the layout of the original data and the required result would help.

This might seem like we're asking for a lot of information to in order to solve this, but if 100 people asked for the same thing, potentially the right solution for each would most likely be slightly different.