Excel Sheet Name Formula 1

[MattSTech]

Is it possible to replace a sheet name with a value from a cell in the following formula?

'What Works
=IF(J4="x",SUM('WITTYSHEETNAME'!L5:L13),0)

Replace WITTYSHEETNAME with the value of Cell B6
'Pseudo Code that doesn't work
=IF(J4="x",SUM('B6'!L5:L13),0)

Thanks in advance,

Matt

 

[AnotherHiggins]

Have a look at the INDIRECT function.

It'll look like this:
[tab][COLOR=blue white]=IF(J4="x",SUM(INDIRECT(B6 &"!L5:L13"),0))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ThomasLafferty]

Try:
=IF(J4="x",SUM(B6,L5:L13),0)
Tom

Live once die twice; live twice die once.

 

[SkipVought]

Hi,

Take a look at the INDIRECT function.


Skip,

_[red][/red]

 

[ThomasLafferty]

Oops! I must have misunderstood what you wanted - John is spot on...
Tom

Live once die twice; live twice die once.

 

[MattSTech]

Thanks for the fast help guys.

I have the following formula now:

=IF(J5="x",SUM(INDIRECT B5 & "!L5:L104"),0)

However, I don't believe it is recognizing INDIRECT B5 as a sheet name. I get the error of #NAME when I put an x in J5.

Any thoughts?

Thanks again,

Matt

 

[AnotherHiggins]

The first thing that I notice is that you started off with B6 in your formula, now you have it as B5. Make sure that your sheetname is in the same cell that is used in the formula.

If that isn't the problem, then the sheet name is different on the sheet tab vs. the cell. Copy 'n' Paste from one to the other.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Crap - I can't read. You are missing parentheses for INDIRECT. Look at the formula I posted again.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Grr. Apparently I can't type, either. I had a parenthesis out of place. The formula should be:
=IF(J4="x",SUM(INDIRECT(B6&"!L5:L13")),0)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MattSTech]

Sorry about that, I have 3 rows that use the formula B4-B6 and I pasted the formula from #5 (Note J5) in the latest post and #6 in the OP.

I checked the spelling and Copy 'n' Pasted to no avail. Would the omission of the single quotes have anything to do with it?

Matt

 

[MattSTech]

I repasted the code:

=IF(J4="x",SUM(INDIRECT(B6&"!L5:L13")),0)

Now I get a #REF? Error

We are close but not yet ther.

Thanks a lot for the fast responses, I can't keep up

Matt

 

[AnotherHiggins]

That error tells us that one of a few certain things happened. From Excel's help file:

"Possible cause: Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas."

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MattSTech]

Ok I think we are on to something. If I make a Sheetname a number (for example 2) then use the above pasted formula it works like a charm. But when I use a word with spaces it causes an issue. How do I add the ' marks in the formula?

Matt

 

[AnotherHiggins]

You are basically just building a string with Indirect. You can use ("text" & cellreference & "more text"). So to add the single quotes, just change to :

=IF(J4="x",SUM(INDIRECT("'" & B6 & "'!L5:L13")),0)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[MattSTech]

YES!

Thank you so much!!

Have a star sir.

Matt

 

[AnotherHiggins]

Thanks. Glad you got it sorted.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.