Changing Formulas a simpler way?

[pikapika13]

I have a workbook with 13 worksheets: labeled January-December, and on
more worksheet that is linked to a particular month (to get a
overview). An exmaple of this formula from that overview workshee
looks like this:

=IF(NOT(ISERROR(INDEX(June!$B$2:$L$418,MATCH($A3,June!$G$2:$G$364,0),4))),INDEX(June!$B$2:$L$418,MATCH($A3,June!$G$2:$G$364,0),4),"")&IF($K3=0,"No
Found","")

The function of this formula is not important, but as you can see...it
a big hassle for me to change June to July. Currently I am doin
"Control H" to replace "june" to "july".
Is there an easy way where I can use one cell to input the particula
month and it will change "june" to "july" in my formulas without VBS

 

[Peo Sjoblom]

You can use indirect


=IF(NOT(ISERROR(INDEX(INDIRECT("'"&B1&"'!$B$2:$L$418"),MATCH($A3,INDIRECT("'
"&B1&"'!$G$2:$G$364"),0),4))),INDEX(INDIRECT("'"&B1&"'!$B$2:$L$418"),MATCH($
A3,INDIRECT("'"&B1&"'!$G$2:$G$364"),0),4),"")&IF($K3=0,"NotFound","")

where B1 would hold the name of your month, now when you would change month
you just type in a new month in B1

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[pikapika13]

Thanks for the reply Peo. I pasted the formula and changed B1 t
L1...and it doesn't seem to work.
Lets simplify the example:
Sheet1 has this formula in A1: =June!$A$1
How would I write this so if I use cell B1 in Sheet1 to be an inpu
cell

 

[Peo Sjoblom]

Ok

=INDIRECT("'"&B1&"'!A1")

assuming you put June in B1 it returns the same as

=June!$A$1

note that I add "'" before and after, that is to make it more generic since
it will work
with sheet names with space and without spaces whereas

=INDIRECT(B1&"!A1")

Will work for your example but not for a sheet name like Jan 2004

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[foolio]

For example... on a brand new work book

Sheet 1

On cell A1 this formula, =Sheet2!$A$1

Sheet 2

On cell A1 type something in .....

Now whatever is typed in sheet 2 a1 shows up in sheet 1 a1 or whereve
you type that formula...

Hope this is what your looking fo

 

[pikapika13]

Got it to work! Thanks foolio and Peo. That made my day...