Dynamic range names and Formulas

[rschoenb]

Help

How do i use a dynamic name range to replace static cell names? I have been trying with no such luck.

1. I have created =OFFSET(IssueType!$A$2,0,0,COUNTA(IssueType!$A:$A),1) (NOT sure if its correct) Which I am hoping should start at A2and add all cells below).

2. If I use the name rage how do I actually use it within a formula?

Example:

=COUNTIFS(general_report!"IssueType!,"=Incident", general_report!F$2:F$297,"=P1",general_report!G$2:G$297,"=Resolved")

Also using the dynamic name range would I still need to use the worksheet name like above which is general_report!

Thanks

[FDibbins]

When you use a named range in a formula, you dont include the sheet name.

Assume this on sheet2 in cells A1:A3...

	A
1	1
2	2
3	3

	A	B
20	6	=SUM(sheet2!A1:A3)
21	6	=SUM(sumrange)

I gave sheet2 A1:A3 the range name sumrange, so you can see how it works. In fact, once you have a named range, and highlight the exact range, the A1:A3 changes automaticaly to sumrange

[mikeTRON]

Your offset formula starts at range A2 but counts EVERYTHING in column A, so if there is something in cell A1 then the named range will be one row too long. IF THAT IS THE CASE YOU CAN FIX IT WITH EITHER OF THESE:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

To confirm the namedrange is working correctly you can type it the little white window to the left of the formula bar that cells A1.
Type the namedrange and hit enter and it will select it.


As for using it in a formula, you just use the named range instead of a fixed range
You just use them like you would a cell reference... So instead of typing "IssueType!$A$2:A297" you type "DynamicRange" or whatever

http://www.excel-easy.com/examples/n...-formulas.html

[rschoenb]

So A1 hold the name of the cell that's why I started at A2 your saying if I use =OFFSET(IssueType!$A$2,0,0,COUNTA(IssueType!$A:$A)-1,1) this would be correct? Thanks!

[rschoenb]

When I use this formula:

=COUNTIFS(IssueTyp,"=Incident", general_report!F$2:F$297,"=P1",general_report!G$2:G$297,"=Resolved")

A window pop's open like it is looking for a file. "IssueType" is actually the range name I've set though. Not sure why though.

[FDibbins]

I think it might be easier to offer suggestions if we could see a sample of what you are working with?

[FDibbins]

1. you cannot use spaces in a named range, so it things that IssueTyp is a file. You will probably find that your range name is Issue_Typ

2. you need to remove the = in your criteria (unless, the data actually contains =)

=COUNTIFS(IssueTyp,"Incident", general_report!F$2:F$297,"P1",general_report!G$2:G$297,"Resolved")
also, if P1 is a cell ref, remove the "", if it is a text entry in a range, then thats fine

[rschoenb]

Ok I tried that. :/ I renamed my dynamic range to just issue. I've attached a copy of the spread sheet a file window pop's up.

=COUNTIFS(Issue,"Incident", general_report!F$2:F$297,"P1",general_report!G$2:G$297,"Resolved")

Thanks for your help..

Ruth

[rschoenb]

I've attached my spread sheet. I am not sure why it keeps trying to open a file or folder when I use the dynamic name. It doesn't look like I have to use the "!" after the name. I've changed the dynamic name for this range to just "issue" to make it simple but it still just doesn't work. :/

[misrasomendra]

Hi,

Your formula:

=OFFSET(issue!$A$2,0,0,COUNTA(IssueType!$A:$A)-1,1)

Refers to issue and Issuetype sheet which are not there in your sample file. Might be that is causing the problem.

Try below formula:

=OFFSET(general_report!$A$2,0,0,COUNTA(general_report!$A:$A)-1,1)

Regards,

[rschoenb]

That makes sense I thought the "Issues" was actually related to the name of the cell not the workbook tab! I will give this a try.

Thanks,

Ruth

[rschoenb]

Ok I just tried this and now get "#Value!" in the cell. This is the formula I used =COUNTIFS(issue,"Incident", general_report!F$2:F$297,"=P1",general_report!G$2:G$297,"=Resolved")

ManageName-Issue.JPG

I've attached the image of my entry within Name manager. SO the rule is called "issue" but using the worksheet general_report.

[misrasomendra]

Try below formula:

=SUMPRODUCT((issue="Incident")*(general_report!F$2:F$292="P1")*(general_report!G$2:G$292="Resolved"))

Regards,

[rschoenb]

Actually that didn't work when I added I didn't get the #VALUE! error however it filled the cell like it was text typed and did not sum. I thought at first I left out the "=" in front of SUMPRODUCT but that was not the case. :/sumproduct.JPG

[FDibbins]

This thread has been going for a while now, what exactly are you ttrying to do, where do you want the answer, and where is that answer coming from?

[rschoenb]

So I am trying to use Dynamic Name Ranges.
Under Named Manager I have the following setup name.

Name: issue
Refers to: =OFFSET(general_report!$A$2,0,0,COUNTA(general_report!$A:$A)-1,1)

When I try to use the dynamic Name "issue" it's not working.

Before change:
=COUNTIFS(general_report!A$2:A$2000,"=Incident", general_report!F$2:F$2000,"=P1",general_report!G$2:G$2000,"=Resolved",general_report!B$2:B2000,"MONOPS*",general_report!B$2:B2000,"MONOPS*")

After change:
=COUNTIFS(issue,"=Incident", general_report!F$2:F$2000,"=P1",general_report!G$2:G$2000,"=Resolved",general_report!B$2:B2000,"MONOPS*",general_report!B$2:B2000,"MONOPS*")

This causes the cell to have a "#VALUE!" error. I want to be able to grow or shrink the fields because the data will change and I really do not want to have to "find/replace" all references to the end cell #'s. Dynamic naming seems straight forward but I am not sure if its my offset that is wrong or how I am using the name in the formula which I don't think I am.

The actual data starts on cell A2 because cell A1 is the heading.

=OFFSET(general_report!$A$2,0,0,COUNTA(general_report!$A:$A)-1,1) --- My understanding is the first $A$2 is actually starting on cell A2. If that is the case I am also -1 close to the end which may have caused it to start on A3 but I also reset the =OFFSET to =OFFSET(general_report!$A$1,0,0,COUNTA(general_report!$A:$A)-1,1) thinking -1 would change it to start on cell A2. That didn't fix the problem though so I am at a loss.

Thank you!

Ruth S.

[FDibbins]

In name Manager, all that is there is "Issue"
Refers to =OFFSET(issue!$A$2,0,0,COUNTA(IssueType!$A:$A)-1,1)

Sheet Issue Type does not exist

However, that still does not tell me what you are trying to achieve I know this may sound dumb, but what Im looking for is something like...

Im trying to count all the aa's in column A, where column B contains bb and column C contains cc.

Once we have the basic formula figured out (in a defined/precise range), then we can start getting fancy using named/dymanic ranges

[misrasomendra]

Hi,

See your file. Formula is working in C3.

Regards,