I have a workbook with 12 sheets that have raw data (IP address Subnets) on them. I have each of these sheets using a named range so I can reference the data later easily. There are actually several named ranges per sheet, I'm focusing on just one for the purpose of the question.
Then I have my Info sheet that has different tables listing information about all of the raw data sheets.
One of the tables is referring to each of the named ranges. Right now I have the named range typed in, but I'm going to be adding more raw data sheets and want to refer to the named ranges dynamically.
The way it is setup now:
The raw data sheets are named based on the IP Subnet information held on the sheet. The named range is named for the sheet.
For example: Sheet Name: "Data_10.1" = Named Range Name "NewIP_10.1": Data10.1!K1:K400
On my Info sheet I have a table that is telling me how many new IPs were found today, it looks like this:
| IP Range | New IPs |
|---|---|
| 10.1 | =COUNTIF(NewIP_10.1,"Add") = 10 |
| 10.10 | =COUNTIF(NewIP_10.10,"Add") = 3 |
I would like to change that formula to be dynamic so that it looks something like:
=COUNTIF(CONCAT("NewIP_",A2),"Add")
This however did not work.
Is there a way to call a named range dynamically?
CodePudding user response：
Use INDIRECT:
=COUNTIF(INDIRECT("NewIP_" & A2),"Add")
Note that INDIRECT is volatile and recalculates every time Excel recalculates. It should be used sparingly or avoided if possible.