Help needed with Indirect() and code to reference another worksheet

[MyNameIsNovice]

I believe I am having difficulty with the Indirect() function and getting Excel to return what I wish. The idea is to be checking to see if all pieces of data for each client has come in for the month. If there is at least one piece that hasn't come in I want to know it, if all have come in I want to know that. Countblank will accommodate this. The formatting of this needs to remain basically the same, meaning the lookup table, the worksheet names, the vlookups to return row positions. What certainly can change is the code I am using via Indirect() if there is a better method.

I am attempting to replicate the following which I know works correctly

Please Login or Register  to view this content.

This code is going to be on the worksheet many times, referencing many different columns and rows so I am trying to use a combination of INDIRECT and some Vlookups to return the correct information. I can write most of it via Indirect correctly, at least I think so.

Please Login or Register  to view this content.

However this currently returns #REF. I think that my issue is with the

Please Login or Register  to view this content.

part of the code, and for the life of me I can't figure out how to get that to work. I could also be using incorrect parenthesis. The formula correctly looks like this if I F9 pieces of it:

Please Login or Register  to view this content.

It looks like it is correctly evaluating the two snippets. Any ideas how I can get this to return the correct code referenced above.

Thanks so much!

[tim201110]

________ _________

[MyNameIsNovice]

tim201110 - wow, thanks. So easy!

[FDibbins]

Not totally sure what you are doing, but I think your pproblem is coming from the merged cells in Data Loads Column A

In plain english, what exactly are you trying to do?

If you can use a helper column to overcome the merged cells, you could use this approach...

I used H, put this in H3, copied down...
=IF(A3="",H2,A3)

Then use this whefre you want the answers...
=COUNTIFS('Data Loads'!$H$3:$H$8,Forumla!$A3,'Data Loads'!$G$3:$G$8,"")

edit: Tim, your formula produces error when copied down?