Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

INDIRECT with a table row

Posted on 2011-09-19
7
Medium Priority
?
315 Views
Last Modified: 2012-05-12
Greetings,

I am creating a conditional formatting formula in VBA. I have the following formula working just fine:

=INDIRECT(""D""&ROW())=""Employee""

In this case, the entire row is shaded when column D and the current row is equal to Employee.

I now want to make this work on a bunch of different data tables where the Status Column = Mapped. I am struggling on how to make this formula dynamic enough to reference the row in the data table where the value of Status = Mapped. I am at a loss at the moment on how to do that most efficient.

I see the formula looking something like =indirect( <the Status column> & ROW()) = "Mapped". I do not know how to properly address <the Status column>

Any tips would be appreciated.
0
Comment
Question by:ckelsoe
  • 4
  • 3
7 Comments
 
LVL 50
ID: 36564042
Hello,

why are you using Indirect at all?

If you want to format a cell in the same row, then just enter the relative reference for the row, for example, if the selected cell is in row 2 when you define the conditional format, use

=$D2="Employee"

As for the Status column: does the position of the status column differ in the sheets? Do you need to identify which column holds the status values?

This would be a lot easier to understand if you could post a sample file. Make sure to replace confidential data with dummy text.

cheers, teylyn
0
 

Author Comment

by:ckelsoe
ID: 36564189
I am need to do this as conditional formatting as the tables are imported data with unknown size and column locations. conditional formatting is a vba free way of achieving the required results.
0
 
LVL 50
ID: 36564219
Yes, but why the Indirect?

See the attached file. Sheet1 has two conditional formatting rules.

blue for "employee" in column D
=$D2="employee"

gold for "mapped" in the column that has "Status" as the column header
=INDEX($A2:$F2,MATCH("Status",$A$1:$F$1,0))="mapped"

Sheet2 has some data as well, but the Status column is not in column F. Copy Sheet1, cell A2, then select the table in Sheet2 and use Paste Special > Formats to paste only the formatting.

The correct rows will be highlighted.

Is that something you could work with?

cheers, teylyn
Book6.xlsx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:ckelsoe
ID: 36564677
That should work. I will play around with it.

I went with Indirect because of this information: http://www.asap-utilities.com/blog/index.php/2005/11/07/conditional-row-color-based-on-a-cell-value/

I just need to get the resulting condition applied from a VBA module that is able to work no matter what size the table is and no matter what column the column Status is located at.
0
 

Accepted Solution

by:
ckelsoe earned 0 total points
ID: 36581276
This is the code that I used to solve this problem that works well.


Range("TheRange").Select
                Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                  "=INDIRECT(""D""&ROW())=""Employee"""
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                With Selection.FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent3
                    .TintAndShade = 0.399945066682943
                End With
                Selection.FormatConditions(1).StopIfTrue = False

Open in new window

0
 

Author Closing Comment

by:ckelsoe
ID: 36708095
This solution achieved the desired results
0
 
LVL 50
ID: 36708419
I know this question is closed.

For posterity:

Indirect is not required to address a cell in the same row

=INDIRECT("D"&ROW())="Employee"

This formula is overly complicated. Depending on my mood, I'd call it anywhere between "amateur" and "rubbish". It works. That's indisputable. But a much cleaner way of achieving the same result is

=$D2="Employee"

when the formula is applied in row 2. When using conditional formats, relative addresses are relative (surprise!) to the current cell. Copied to another row, that formula will adjust to the relevant row, without the overhead of an overly complicated INDIRECT() with a nested ROW(). Two additional calculations that Excel has to perform per row.

If you want fast performing worksheets, keep the overhead to a minimum.

cheers, teylyn
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

885 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