Dynamic Name Range (Blank Cells) / OFFSET & COUNTA #VALUE! Error

[Creation]

Good afternoonall,

First off, apologies in advance for the long post and maybe long windedexplanation.

I have asummary which was created using a combination of SUMPRODUCT, MATCH & ROWfunctions with the good help of Marcelo Branco - This works fine and theresults are as expected.

However, I hadnoticed that when new raw data was pasted over the existing data the SUMPRODUCTformulas were not dynamically updating the range data I.e. If the data had 61000rows before and then new data pasted contained 61300 rows - the SUMPRODUCTwould still point to 61041. Example of my SUMPRODUDCT formula below:

=SUMPRODUCT(--('Retail Postings'!K2:K61041="NR")+('RetailPostings'!K2:K61041="PR"),--('Retail Postings'!AH2:AH61041="Real"),--(MATCH('RetailPostings'!B2:B61041,'Retail Postings'!B2:B61041,0)=ROW('Retail Postings'!B2:B61041)-ROW('RetailPostings'!B2)+1),'Retail Postings'!I2:I61041)

To overcome theabove, I did some research and quickly realised that my best bet was to create DynamicRange Names using the OFFSET & COUNTA formula. This works awesome and fixedthe issue. Example of my DNR formula below:

=SUMPRODUCT(--('RetailPostings.xlsx'!RET_STATUS="NR")+('Retail Postings.xlsx'!RET_STATUS="PR"),--('RetailPostings.xlsx'!RET_TR_NUMBER="Real"),--(MATCH('Retail Postings.xlsx'!RET_ACCOUNT_NUMBER,'RetailPostings.xlsx'!RET_ACCOUNT_NUMBER,0)=ROW('Retail Postings.xlsx'!RET_ACCOUNT_NUMBER)-ROW('RetailPostings.xlsx'!RET_ACCOUNT_STATIC)+1),'Retail Postings.xlsx'!RET_BALANCE)

Now, this iswhere is get’s messy and where I am absolutely stuck! (Someone PLEASE HELP ME).


‘Column AH (DNR – RET_TR_NUMBER)’ has the following formula in the ‘Name Manager Refers To’ box =OFFSET('Retail Postings'!$AH$1,1,0,COUNTA('RetailPostings'!$AH:$AH)-1,1). This is all good and well, however, I am gettingthe ‘#VALUE!’ error and have figuredout that this is because ‘Column AH(DNR - RET_TR_NUMBER)’ contains thousandsof blank cells in the column which contain no text, number or characterswhatsoever. I was able to come to this conclusion by filtering the column toblanks only, entering some text, filling down and voila the #VALUE! error was gone and the expectedresult was achieved.

My issue withthe above is the cells in AH which are blank are supposed to be this way. So myquestion, is there any way I can make the OFFSET & COUNTA formula work sothat I can achieve the expected result with blank cells included in the column?

APOLOGIES inadvance for the long post but I wanted to provide as much as information aspossible, as it’s nearly impossible for me create a sample of data to share.

Thank you inadvance!!!!

 

[BlueHornet]

Do you have any columns in the data table that always contain an entry? There's no reason why the OFFSET can't refer to that column instead, since all you need is a count of rows to act upon.

 

[Creation]

Hi Buddy, wow I am surprised someone understood my post lol.

In answer to your question - yes I do. In fact all of the other columns in the data table contain an entry (either text, number or a mix). Column AH is the only one that contains blanks as well as cells with Text, Numbers etc.

So do you suggest I point the OFFSET to any another column that does contain an entry instead of AH?

 

[BlueHornet]

Sure. Give it a try. You'll want the dynamic range to refer to the same range (that you expect), but you just want a better count of the rows than you're getting with your COUNTA of a column that contains blanks. So count the values in some other column instead.

PS: Don't imagine that I understood your entire post—I didn't—but I did get that much, anyway.