Named range in cell reference

[KGee]

This should be simple but I can't get the right syntax. I need to figure out the last row in column B and parse a string value from the text in that cell. I created a named range (dcLastRow) to return the last row (132 in current report) and want to use that named range within my cell formula. I've tried different variations of the syntax below but nothing is returned or I get a #name error.

=MID("B" & dcLastRow,6,3)

 

[Exceladd1ct]

Try:

 =MID(dcLastRow,6,3)

 

[Joe4]

A named range in Excel or in VBA?
How are you trying to apply this formula, directly on the sheet, or via VBA?
If you are trying to write the formula to a cell via VBA, would you would use something like:

Range("A1").Formula = "=MID(B" & dcLastRow & ",6,3)"

 

[KGee]

The formula is directly in the sheet. I thought I might need to escape with quotes but couldn't get it to work either.

 

[Joe4]

If dcLastRow is a named range in Excel, it is not an integer (like 132), but rather, it is a range. So, you wouldn't attach a "B" to it, as it already has a column and row component.
It would be just as Exceladd1ct posted.

What exact range is dcLastRow?

 

[KGee]

When I use Exceladd1ct's formula it sees the text I am trying to parse out as "132" which is the last row number in the current report.
When I use the formula below, it sees the text I am trying to parse out as "B132" which is the cell I want the formula to look at.

=MID("B"&dcLastRow,6,3)

I tried inserting the formula to get the last row into my formula but the results were the same. How do I get the formula to recognize B132 as a cell address and not text?

 

[Joe4]

Please explain exactly how you have set "dcLastRow".
If you go to the Formulas menu, and click on the Name Manager in the Defined Names ribbon, do you see dcLastRow there?
If you do, select it, and tell me what it shows in the "Refers to:" box at the bottom of the screen.
If you do not see it there, then there is no such named range named "dcLastRow".

 

[KGee]

I appreciate the help, but maybe I'm not explaining it clearly. These reports are dynamic so the last row will always change. In the current report, my last row with data is row 132 so I need to parse a string from the contents of cell B132. Manually typing the formula below gives me exactly what I need.

=MID(B132,6,3)

My problem is that B132 will not always be the last row so I need a dynamic formula. I added a formula in another cell which I named dcLastRow which simply returns the last row number from column B. It's in B1 and the value is 132 in the current report. I don't need the named range per se, I was just trying to get the row number and use that value in my formula. When I look at the function arguments in the cell where I have the "MID" formula, it is not reading the contents of cell B132, it is treating B132 as a text string of four characters so the formula returns nothing since I am telling it to start at the sixth character and it sees B132 as a four character string and not a cell address.

 

[FormR]

I added a formula in another cell which I named dcLastRow which simply returns the last row number from column B.

You can probably skip some of those steps - but using your current set-up you could try:

=MID(INDEX(B:B,dcLastRow),6,3)

 

[Joe4]

I see, so the named range contains a formula that returns your last row number. In that case, just use the INDIRECT function, i.e.

=MID(INDIRECT("B" & dcLastRow),6,3)