Referencing with Indirect

**RBI** · 09-26-2008, 01:21 PM

The Question I have is with the "indirect" function... I was wondering if there is anyway to include Sheets and maybe workbooks into the function:

i.e.
'Sheet1'!C3="Apples"

(lets pretend the following cells are on sheet2)
Cell A1= 'Sheet1'!
Cell A2= C
Cell A3= 3
Cell A4=A1&A2&A3

=in theory, indirect(A4) would have an expected reference to
'Sheet1'C3 (= "Apples") but instead i get invalid Reference error.
I was wondering if there is any solution to this as it is a very inportant piece in my work and this ability would be a great asset.

Thanks for the time

**VBA Noob** · 09-26-2008, 01:27 PM

Try

Sheet1!

in A1 instead

VBA Noob

**RBI** · 09-26-2008, 01:51 PM

i tried like you said to drop the ' ' around the sheet name still get the same error... did you try it ? is there another posible way?

**VBA Noob** · 09-26-2008, 01:57 PM

Yes. It worked for me see example

This also worked

'Sheet1!

VBA Noob

**shg** · 09-26-2008, 02:04 PM

You only need the single quote in the sheet name contains spaces -- but then you need two, because Excel interprets the first as a text delimiter. To accommodate either case, use the quote.

So enter

''Sheet1'!

... which appears as

'Sheet1'!

... and you're golden.

**RBI** · 09-26-2008, 02:29 PM

oh I C... My problem was that my sheet name had a space in it so the '' ' around the sheet name was required... my bad for not giving you the specifics... thanks for all your help guys really appreciate it.

Cheers