Solved: Is there a formula solution?

[stanl]

This is not my problem, but if



I have a workbook with 4 sheets. I want to go through sheet 1 and test to see if the value of RowX ColumnY exists in sheets 2 and 3, if so then I want to write the entire row to sheet 4. This would be done all the way down column Y until NULL.


while iterating the values in the cells in sheet1, is there some use of index/match etc.. to compare values in sheets 2,3 rather than poking the values by referencing both sheets via sheet.cells(r,c).value

Hope this makes sense... Stan

[stanl]

I guess the answer is NO.

[Aussiebear]

Stanl, for those of us who are mentally challenged.... can you post an example please

[stanl]

Using Values in Column B and sheet1, compare to values in sheets 2/3 and copy entire row to sheet 4 if valid. The question was not whether this could be done, but could it be done with a formula rather than writing a For Each... or While loop in code.

Stan

[SamT]

Plug this into A1 on sheet4, then copy or fill it where you want

=IF(Sheet1!$B1=Sheet2!$B1,IF(Sheet1!$B1=Sheet3!$B1,Sheet1!A1,),)

[Shazam]

Try...


Input formula in Sheet4 cell A2 custom format cell with:

0;-0;;@

then fill across then copy down.

=IF(OR(Sheet1!$B3=Sheet2!$B2,Sheet1!$B3=Sheet3!$B2),Sheet1!B3,"")


But if you need to look in a range of cells then input formula in sheet4 cell A2 fill across and copy down.

=CHOOSE({1,2},IF(SUMPRODUCT(--(Sheet1!$B$3:$B$30=Sheet2!$B2))=0,"",Sheet1!B3),IF(SUMPRODUCT(--(Sheet1!$B$3:$B$30=Sheet3!$B2))=0,"",Sheet1!B3))


Hope it helps!

[stanl]

Thanks all.... Again, the original problem is not mine, it was posted on a non-VBA BBS by a user looking for a programmatic solution. I thought he might consider a formula and the question raised my own curiosity

Stan