CountIF Formula Referencing Dynamic Worksheets

[reberryjr]

I have a workbook with an Inventory worksheet and several other worksheets named as job ids (0001, 0002, etc). When the User enters new job details onto a form, the form will create a new worksheet and name it as the next sequential number. I'm trying to add a formula to the Inventory worksheet that will access the job worksheet with the same name as the job id listed in the corresponding row of where the formula is being entered. I figured I would try to enter the formula in Excel (without VBA) first, then try to convert it to VBA. I can't seem to get the formula right (it returns a #REF! error). Here's the formula I've been working with (found it while researching this issue)

=COUNTIF(INDIRECT("'"&B3&"'!J12:J500"),$Y$2).

This formula does work, but I don't want to add the formula manually every time a new job is created.

=COUNTIF('0001'!J12:J500,$Y$2)

File details:

1. Formula goes in column Y of the inventory worksheet
2. Individual job ids are stored in column B of the inventory worksheet
3. Data is formatted as #### so a value of 1 appears as 0001
4. Job worksheets are named 0001, 0002, etc.
5. The J12:J500 reference isn't ideal. I would like for it to run J12:J and the last row, but I figured I would add the last row bit when I converted the formula to VBA.

Thoughts?

 

[Fluff]

Try

=COUNTIF(INDIRECT("'"&text(B3,"0000")&"'!J12:J500"),$Y$2).

 

[reberryjr]

Try

=COUNTIF(INDIRECT("'"&text(B3,"0000")&"'!J12:J500"),$Y$2).

@Fluff as usual, your awesome! Associated questions: 1) Instead of defining the end of the range as J500, is there a way for me to do something to the effect of J12:LastRow? What's being counted will differ from job to job. Sometimes it would be J12:J20 or J12:J85. If needed, I can "add/update" the formula when the workbook opens.

 

[Fluff]

If there is no other data below row 20 or 85 or whatever, then you can just use J500

 

[reberryjr]

If there is no other data below row 20 or 85 or whatever, then you can just use J500

I really don't see anything going past 500. I guess to be safe, I can say J1000. Last question...how in the heck did you learn all of this stuff? You always seem to have the right answer. I have to tackle problems as I encounter them, and often have to reach out for help.

 

[Fluff]

I've learnt most of what I know from posting here & seeing how other people do things.