Indirect Formula

[blindfold]

Hi,

I'm new to using the Indirect function within Formula, and I am currently having the following problem.

I have a workbook containing a worksheet for each week of the year starting from week 18 (Week 18, Week 19, Week 20 and so on) which are identical. The is also a 'Summary' worksheet.

Within a cell on the 'Summary' sheet I have the following IF formula which returns the correct data;

=IF(OR('Week 18'!D7="OFF",'Week 18'!D7="HOL",'Week 18'!D7=" ")," ",'Week 18'!D7)

However I now wish to change this and make all occurances of 'Week 18' become 'Week' & what ever week number is entered into F5 on the summary worksheet, depending on which week it is. I beleive this can be done with the use of Indirect function within the formula, something like this;

=IF(OR(INDIRECT(('"Week"&F5&"'!D7)="OFF"),INDIRECT(('"Week"&F5&"'!D7)="HOL"),INDIRECT(('"Week"&F5"'!D7)=" ")," "),INDIRECT('"Week"&F5&"'!D7))

But no matter how many times I try to write the formula or move the ()!'"" characters the formula always returns #REF!.

If anyone has any suggestions on how I can correct this it would be greatly apprecaited.

Regards

 

[Tuelor]

Should be Indirect("SheetName!A1") = "OFF"only - looks like you have to much in the brackets.

D

 

[Aladin Akyurek]

Try something like...

=IF(ISNUMBER(MATCH(INDIRECT("'Week "&F5&"'!D7"),{"OFF","HOL"," "},0)),"",
     INDIRECT("'Week "&F5&"'!D7"))

By the way, a space (i.e., " ") as an entry is not very useful, unless there is a good reason for it to have.

 

[Tuelor]

Your nearly there

 

[blindfold]

Thanks Aladin!! Much Obliged


http://www.mrexcel.com/forum/member.php?u=10

 

[repairman615]

Hello,

Welcome to the Board!!

Try:

<TABLE style="WIDTH: 782pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1042><COLGROUP><COL style="WIDTH: 782pt; mso-width-source: userset; mso-width-alt: 19053" width=1042><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 782pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=1042>=IF(OR(INDIRECT("'Week "&F5&"'!D7")="OFF",INDIRECT("'Week "&F5&"'!D7")="HOL",INDIRECT("'Week "&F5&"'!D7")=" ")," ",INDIRECT("'Week "&F5&"'!D7"))

</TD></TR></TBODY></TABLE>

 

[T. Valko]

Hi,

I'm new to using the Indirect function within Formula, and I am currently having the following problem.

I have a workbook containing a worksheet for each week of the year starting from week 18 (Week 18, Week 19, Week 20 and so on) which are identical. The is also a 'Summary' worksheet.

Within a cell on the 'Summary' sheet I have the following IF formula which returns the correct data;

=IF(OR('Week 18'!D7="OFF",'Week 18'!D7="HOL",'Week 18'!D7=" ")," ",'Week 18'!D7)

However I now wish to change this and make all occurances of 'Week 18' become 'Week' & what ever week number is entered into F5 on the summary worksheet, depending on which week it is. I beleive this can be done with the use of Indirect function within the formula, something like this;

=IF(OR(INDIRECT(('"Week"&F5&"'!D7)="OFF"),INDIRECT(('"Week"&F5&"'!D7)="HOL"),INDIRECT(('"Week"&F5"'!D7)=" ")," "),INDIRECT('"Week"&F5&"'!D7))

But no matter how many times I try to write the formula or move the ()!'"" characters the formula always returns #REF!.

If anyone has any suggestions on how I can correct this it would be greatly apprecaited.

Regards

Try it like this...

=IF(OR(INDIRECT("'Week "&F5&"'!D7")={"OFF","HOL",""}),"",INDIRECT("'Week "&F5&"'!D7"))

 

[Aladin Akyurek]

Thanks Aladin!! Much Obliged

You are welcome. Thanks for providing feedback.

A side note. IsNumber/Match with multi-tems sets is immensely superior
to expressions using OR...

 

[T. Valko]

IsNumber/Match with multi-tems sets is immensely superior
to expressions using OR...

Define immensely.

When there are only a "few" variables as in this case the difference is negligible.

=OR(A1={"x","y","z"})

Average calculation time for 5 calculations: 0.00025 seconds

=ISNUMBER(MATCH(A1,{"x","y","z"},0))

Average calculation time for 5 calculations: 0.00024 seconds

The difference is not immense.