Excel - reference a cell with a tab name to be used in formula 1

[wz]

I hope I can explain this well enough...

I have a worksheet tab called "5 8" (it stands for May8)

I have a worksheet tab called "total"

I want to reference tab "5 8" inside a formula on the total worksheet. I know I can use ='5 8'!A1 and get the info from the cell (A1) into the total worksheet. However I want to go a step further and reference a cell on the total worksheet to get the "5 8".

I want the end user to enter the date 5 8 on the total worksheet & then have the data from the worksheet "5 8" fill in.

I tried ='(A1)'!A1

(The workbook has many worksheets and I want to reference only some - without the enduser typing or re-typing formulas.

 

[GlennUK]

Use the INDIRECT function, something like:
=INDIRECT(&"'"&A1&"'!A1")


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[wz]

Thanks although I don't believe that is what I am looking for.

I am working with an enduser who is not very computer literate & I am using a advanced filter & using code to copy and paste data to a specific column.

I want the enduser to place the title of the worksheet name (tab name) in a cell & then I want the formula in the cell below it to reference the cell that contains the worksheet name. The formula will pull a cell in the named worksheet.

example:
sheet1 (tab name: Product)

a1: 5 8 (end user keys this in)
a2: formula to lookup a1 on this sheet & use it to find tab & report back a1 (a1 on tab 5 8)
formula like: '5 8'!A1 (except I want it use whatever the enduser keys in a1 (this is going to change each week)


sheet 2 (tab name: 5 1) stands for May 1
a1: Product 3731
a2: Product 5567


sheet 3 (tab name: 5 8) stands for May 8
a1: product 2473
a2: product 2940

 

[Gavona]

Glenn's solution sounds perfect to me. Have you tried it?
From your description you only ever return the values from A1 on the other sheets. Not sure why you have told us the value in A2 on those sheets.

Gavin

 

[wz]

Thanks to both of you - However I am still not having any luck. Yes I have tried formula.

I am attaching a sample file (the reason for the a2 line of data is because I have about 40 rows & plan to duplicate this formula to each row)

 

[SkipVought]

"Yes I have tried formula."

And????

What happened? Just saying, I tried and it didn't work!" is an incomplete worthless statement.

EXACTLY WHAT did you try? Each different WHAT.

EXACTLY WHAT were the results? For each different WHAT.

In fact, if you tried SEVERAL thing and then threw up your hands, I would be more encouraged my your persistence.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dallen43]

Okay, I didn't create the original post, but when I see an interesting solution I try it out so I can learn something.

When I tried it, I received the error message "The formula you typed contains an error".

I'm using Excel 2003 SP3.

I'm sure there is a comma or quote wrong but I can't figure it out. I did copy the formula from Glenn's post.

 

[SkipVought]

"When I tried [red]it[/red]..."

What was [red]it[/red]???

and EVERYTHING about [red]it[/red].



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dallen43]

Skip,

I don't think I deserved to be slapped like that.
I thought I was perfectly clear.
I set up the worksheets with the data as indicated in the original post. I copied the formula as written by Glenn and got the error I listed above.

I'm not the person who originally posted and didn't even try Glenn's formula. So, pleae excuse me for just trying to learn something.


Deb

 

[SkipVought]

I make no assumptions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Guys guys, calm down. Step 1 of problem solving is assuming there is a problem. Step 2 is assuming you can fix it.

=INDIRECT([b][u]&[/u][/b]"'"&A1&"'!A1")

The first ampersand is incorrect. The rest of the formula is exactly correct.

Glenn probably typo'd the formula, and it's an easy mistake to miss, fortunately it's also an easy mistake to FIX. Just stop, look at the formula, and figure out everything that it's doing, one step at a time.

wz, dallen. When Excel tells you the formula is typed incorrectly, and you hit ok, it highlights the error or places the cursor AT the error 9 times out of 10! Start there and see if you can figure out what's going wrong.

The & concatenates strings into a single string. the format is "String1" & "String2" and the results is "String1String2"
There was no preceding string before the following, therefor, Excel threw an error. Also of note is that referencing a number will convert it to a string, and referencing a cell will accept the cell's value.

As far as INDIRECT, the definition is ironically direct. All you are doing is taking a cell reference (for example: "Sheet2!A1") and telling the formula to return the value of that reference. All it asks is that you feed the reference as a string, so it must live inside double quotes.

Glenn was using the concatenation operator because the name of the sheet was variable. Additionally, the name of the sheet needs to be in single quotes if it has a space in it. It's a really good idea which using direct to always include that single quote. So, what Glenn was showing:
"'" (single quote)
&
A1 ("5 8")
&
"'!A1" (second single quote, exclamation mark shows that the name of the sheet has finished and now we reference cells, and finally the cell destination.)

Hope that all makes sense

 

[wz]

THANK YOU MR. GRUUUU! and all...

Yes! MR GRUUUU your formula works!! That is exactlly what I needed!

I have a complex workbook with many sheets and many formulas (prob. should be in a database if you ask me). This is going to save someone a ton of time! THANKS AGAIN!

wz

 

[dallen43]

Thank you Gruuuu.
Your explanation is great.

 

[GlennUK]

Thanks Gruuuu, I didn't proofread that formula very well.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Gruuuu]

no biggie