Solved

can a range be made dynamic in Excel 2007/2010

Posted on 2012-03-28
8
204 Views
Last Modified: 2012-03-29
Hello:

Say I have a formula (=COUNTIF($F4:$I4,"<>")=0) for conditional formatting that applies to a well defined range (e.g., =$F$4:$I$34). That range as defined is fixed.

Is it possible to vary the ending row (harcoded as I$34) dynamically?

I am thinking that some cell A1=40 could contain the number of rows to process in the spreadsheet. and that the range could somehow pickup cell A1 (containing integer 40) and somehow update the range automatically. Something like =$F$4:$I$[stick in the value of cell A1 automatically somehow, so to affect the range to $F$4:$I$40 dynamically]

Appreciate your insights.

Thanks and best regards,
Lyteck
0
Comment
Question by:lyteck
  • 4
  • 2
  • 2
8 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37778962
The normal way to do this is using a named range and the OFFSET formula.  While this is OK, I prefer to use INDEX as it is non-volatile and so does not recalculate every time anything in the sheet changes.  It only has to recalc when the workbook opens, or when something really changes in the column in question.

So define a named range with the formula:
=Sheet1!$F$4:INDEX(Sheet1!$F:$F,Sheet1!A$1,1)

Open in new window

and use this in your COUNTIF.
define name
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37778991
Should have said this assumes as you said that the value in A1 is the number of rows in the range in question.  The function works by defining a range that starts with cell you want to start with and ends with the last cells - INDEX gives you a cell from a specific row and col of a range.  It can also do other interesting things if you omit the row or column ref, but you can find that in the documentation or by looking at Daniel Ferry's brilliant article on this: here
0
 

Author Comment

by:lyteck
ID: 37779094
Hi Andrew:

Thank you for the quick reply. I appreciate the detailed walkthrough though I must admit it is a little fuzzy for my current level of understanding of ranges. could you illustrate in my simple test spreadsheet? I have A1=40 and and I have a static range defined in the conditional formatting rules which I would like to make dynamic.

Thanks,
Lyteck
Validation-of-mutually-exclusive.xlsm
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 37780131
I suggest that you apply the Conditional Formatting to all of columns F:I, starting in row 4. Doing so won't increase your file size, but will assure that each row of data that you add receives the correct formatting.

Along with the above suggestion, I changed the formula for your first format condition to:
=AND(COUNTA($A4:$D4)>0,COUNTIF($F4:$I4,"<>")=0)
This is the one for the light yellow color, and the change means that it applies only if columns A through D on that row contain at least one datum, while columns F through I are blank.
Validation-of-mutually-exclusive.xlsm
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 250 total points
ID: 37780374
Hi - looks like byundt has put in the named range you need.  Defining named ranges like this in Excel is useful to know - the Name Manager on the Formulas tab is the place to do it - you can define new names there and edit or delete existing ones.  The names you define can then be used in formulas on the worksheet and can help to make your spreadsheets much more readable.

The only slight change I would make to the formula byundt used:
='Mutually exclusive fields'!$F$4:$I$4:INDEX('Mutually exclusive fields'!$I$4:$I$10000,'Mutually exclusive fields'!$A$1)

Open in new window

, would be to make the INDEX use entire columns - this means that the range will extend to row 30 exactly:
='Mutually exclusive fields'!$F$4:$I$4:INDEX('Mutually exclusive fields'!$I:$I,'Mutually exclusive fields'!$A$1,1)

Open in new window

The difference is because the first formula starts the INDEX range in row 4 it will count on 30 rows from there - the second one counts from row 1.  You can choose which one is more suitable.
0
 
LVL 80

Expert Comment

by:byundt
ID: 37781286
andrewssd3,
Although I created a dynamic named range, I didn't use it in my suggestion for Lyteck's problem. Each time I applied it to the effective range of the Conditional Formatting, I found it was immediately converted into the equivalent fixed range--e.g. $F$4:$I$43. For this reason, I applied the Conditional Formatting to the entire column, starting with row 4.

Dynamic named ranges are a great tool. They just weren't helpful in this problem.

Another approach that I could have used was to put Lyteck's data in a Table by selecting the data (with header labels) and using the Insert...Table menu item. Excel would then apply the formatting automatically as new rows of data were added to extend it.

Brad
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 37781586
No that's right of course - I must admit I missed the reference to conditional formatting in the original post.  If I understand the requirement here (which I'm not sure I do exactly!) dynamic ranges aren't going to help because you'd have to drag down the conditional formatting manually anyway if more rows were added.
0
 

Author Closing Comment

by:lyteck
ID: 37781690
Thank you both Andrew, Brad for your expertise. I think I understand the possible approaches and limitations from your back and forth discussions. I'll apply the conditional formatting to the entire column for now and keep in mind dynamic ranges to solve other problems.

Best Regards, Lyteck
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now