Formula ? Trying not to use " "

[zone709]

Hi I am trying to avoid using "Data "

I need to use Data! without the "". Is there a way to do these formulas different and work the same way. Maybe not using the indirect way. Reason cause I ahev to switch the sheet name sometimes and the " " mess it up thanks

=INDIRECT("Data!$E$2")

=IF(SUM(INDIRECT("Data!E"&(INT((ROW()-4)/7)+4)&":G"&(INT((ROW()-4)/7)+4)))=0,"",SUM(INDIRECT("Data!E"&(INT((ROW()-4)/7)+4)&":G"&(INT((ROW()-4)/7)+4))))

 

[Rick Rothstein]

Hi I am trying to avoid using "Data "

I need to use Data! without the "". Is there a way to do these formulas different and work the same way. Maybe not using the indirect way. Reason cause I ahev to switch the sheet name sometimes and the " " mess it up thanks

=INDIRECT("Data!$E$2")

=IF(SUM(INDIRECT("Data!E"&(INT((ROW()-4)/7)+4)&":G"&(INT((ROW()-4)/7)+4)))=0,"",SUM(INDIRECT("Data!E"&(INT((ROW()-4)/7)+4)&":G"&(INT((ROW()-4)/7)+4))))

As long as you are hard-coding the sheet name into the formula, you can use the INDEX function instead of the INDIRECT function...

=INDEX(Data!E:E,2,)

=IF(SUM(INDEX(Data!E:G,(INT((ROW()-4)/7)+4),))=0,"",SUM(INDEX(Data!E:G,(INT((ROW()-4)/7)+4),)))

 

[Special-K99]

Type the name of the sheet, in this case Data, into a blank cell, let's say A1
Then change the formula and replacing all instances of "Data (note the one double quote) with A1&" (note the double quote)

When you need to change the sheet name, just type a new value into A1

 

[Rick Rothstein]

Type the name of the sheet, in this case Data, into a blank cell, let's say A1
Then change the formula and replacing all instances of "Data (note the one double quote) with A1&" (note the double quote)

When you need to change the sheet name, just type a new value into A1

What about including apostrophes just in case the new sheet name has spaces in it?

 

[zone709]

ok this formula works as I been testing. When I add other sheet name called Data (2). It puts this in already

=INDEX('Data (2)'!E:E,2,)<strike></strike>

So this is great. Was having issue when I had it " ". Thanks for all the replys