How to take the worksheet name as a variable(Label/Define) in a fo
 

Hi,



I have 12 sheets for each month of the year. The Sheets has been names JAN,
FEB, MAR€¦DEC. I added one more Sheet Analysis. In the Analysis Sheet, there
is a list containing 12 entries JAN, FEB, MAR€¦.DEC.





I want to do some calculations based on the user selection(JAN or FEB or MAR
etc). The formula I am using is
=IF(((VLOOKUP(B4,MONTH!$G$2:$G$245,1,FALSE))=B4)," Y"," "); here MONTH should
be a variable based on the selections. Please help me in this( I am expecting
a solution without using macros ). This is an urgent issue.



Thanks and Regards,

Subin

How to take the worksheet name as a variable(Label/Define) in a fo
 

You can use indirect but your formula seems to test if B4 exists in G2:G245,
you can do that easier

=IF(ISNUMBER(MATCH(B4,INDIRECT("'"&I3&"'!G2:G245") ,0)),"Y","")

using your formula

=IF(VLOOKUP(B4,INDIRECT("'"&I3&"'!$G$2:$G$245"),1, FALSE)=B4,"Y","")

however your formula won't return a blank if not found it will return a #N/A
error

In both cases I3 holds the name of the sheet

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Subin" wrote in message
...
Hi,



I have 12 sheets for each month of the year. The Sheets has been names
JAN,
FEB, MAR€¦DEC. I added one more Sheet Analysis. In the Analysis Sheet,
there
is a list containing 12 entries JAN, FEB, MAR€¦.DEC.





I want to do some calculations based on the user selection(JAN or FEB or
MAR
etc). The formula I am using is
=IF(((VLOOKUP(B4,MONTH!$G$2:$G$245,1,FALSE))=B4)," Y"," "); here MONTH
should
be a variable based on the selections. Please help me in this( I am
expecting
a solution without using macros ). This is an urgent issue.



Thanks and Regards,

Subin

How to take the worksheet name as a variable(Label/Define) in
 

Thank you very Much.. Peo Sjoblom. It worked :-)


"Peo Sjoblom" wrote:

You can use indirect but your formula seems to test if B4 exists in G2:G245,
you can do that easier

=IF(ISNUMBER(MATCH(B4,INDIRECT("'"&I3&"'!G2:G245") ,0)),"Y","")