Define name

[David]

Hello,

I have a worksheet with 10 rows table X 3 columns.
Column A: Worker name
Column B: Worker salary
Column C: active/not active (1,0)

I want to define 2 ranges:
First range: all workers name from Column A (just the cells with value)
that are active.
I mean: row A value is not Null and row C value is 1.
Second range: Worker's salary for the workers in the First range.

For example:

A B C
Row1: Gil 120 1
Row2an 100 0
Row3:Joe 50 1

First range will be: A1,C3
Second range will be: 120'50

How can I do this ?

Thank you for your help.
Gil D.

 

[Gil]

Hello,

Thank you for your help. I want to define dynamic ranges because my
user changes the data (add/remove workes and set status to active/not
active).

How can I define these names ?

Gil D.

 

[davesexcel]

http://www.contextures.com/xlNames01.html#Dynamic

Check out this site , it may help

 

[Gil]

Hello,

Sorry, It did not help me to solve my problem.

Gil D.

 

[Guest]

Hi Gil,

Here's an example for making a dynamic range for your table data:

Insert>Name>Define
In the name box enter:
'ActiveSheet'sName'!TableData

This creates a sheet-level name for the active sheet.
To use the name, refer to it as TableData.

In the RefersTo box enter:
=OFFSET(Sheet1!$A$1:$C$3,0,0,COUNTA(Sheet1!$A:$A))

This assumes the table has no header. If it does then use:

=OFFSET(Sheet1!$A$2:$C$4,0,0,COUNTA(Sheet1!$A:$A)-1)


It requires that there are contiguous (non-empty) cells in columnA. You
could do the same for the Salary cells.
-OR-
If you just want to reference the salary 'relative' to a name in columnA
then the RefersTo for Salary would be:
=$B1
so if A3 is active, Salary refers to B3. This will persist the entire length
of columnA.

Regards,
GS