Cell reference from the active sheet?

[luscious]

Hi,

I have an excel conundrum I am looking for a solution to....if there even is one. What it is I have sheet 1, which has a drop down list of one classification of a part, and a sheet 2, that is the same as sheet 1, but the drop down list is for a different classification of part. Sheet 3 has a cell that references the sheet 1 drop down menu value that is run through a formula. Is there a way to have it use the value in the sheet 2 dropdown menu, just by virtue of that is the sheet i am currently looking at? What I mean is, when I am looking at sheet 1, the cell formula on sheet 3 uses the value in the drop down menu for sheet 1. When I am looking at sheet 2, the cell formula on sheet 3 uses the sheet 2 drop down menu value. Is this possible?

thanks!

-Andy

[6StringJazzer]

If you are looking at sheet 1 or sheet 2, you won't be looking at sheet 3 so you can't see the result. When you do look at sheet 3 to see the formula result, you are no longer looking at sheet 1 or sheet 2. Just how do you envision this is going to work?

The only way to get results that vary depending on what sheet is the active sheet is by using VBA.