PDA

View Full Version : Solved: Is there a formula solution?



stanl
10-25-2006, 12:54 PM
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
10-27-2006, 08:38 AM
I guess the answer is NO.

Aussiebear
10-28-2006, 03:11 PM
Stanl, for those of us who are mentally challenged.... can you post an example please

stanl
10-28-2006, 05:28 PM
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
10-28-2006, 06:04 PM
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
10-28-2006, 09:26 PM
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
10-29-2006, 07:37 AM
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