# Using indirect to pull cell reference in other worksheets in ranges

1. ## Using indirect to pull cell reference in other worksheets in ranges

Not sure what I am missing

countif(Sheet1!\$B\$9:INDIRECT("'"&"Sheet 2"&"'!\$F\$5"), "Yes") returns #Ref

Much appreciated for the help

2. ## Re: Using indirect to pull cell reference in other worksheets in ranges

countif(indirect("'Sheet1'!B9:" & 'Sheet 2'!\$F\$5), "Yes")

Maybe is what you were looking for? Is there a cell reference like C12 or something in Sheet 2:F5?

3. ## Re: Using indirect to pull cell reference in other worksheets in ranges

what reference are you trying to create ie what would it look like without the indirect
where is the info you are going to reference in the indirect?

4. ## Re: Using indirect to pull cell reference in other worksheets in ranges

ah crap forgive me.
a long day looking at this spreadsheet has numbed me from common sense...

It should be this
countif(Sheet1!\$B\$9:INDIRECT("'"&"Sheet 1"&"'!\$F\$5"), "Yes")

The range should be on the same sheet.

where F5 in sheet 1 refers to the cell reference, based on a bunch of dependent variables.

5. ## Re: Using indirect to pull cell reference in other worksheets in ranges

I think you want what I posted then, try this

=countif(indirect("Sheet1!B9:" & 'Sheet 1'!\$F\$5), "Yes")

Although, do you really have 2 tabs called Sheet1/Sheet 1?

6. ## Re: Using indirect to pull cell reference in other worksheets in ranges

So say if F5 in sheet 1 returns B15,
it should be generate countif(sheet1!b9:b15)

7. ## Re: Using indirect to pull cell reference in other worksheets in ranges

Pretty much. Putting it around just a basic cell reference is unnecessary. You want it to wrap around the first reference since part of it is text and part of it is a cell reference. Then it should return the correct range of values.

8. ## Re: Using indirect to pull cell reference in other worksheets in ranges

ah i see.

Many thanks!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1