Dynamic Named Ranges

  • Thread starter Thread starter Sam Benson
  • Start date Start date
S

Sam Benson

Hello

Is it possible to define a dynamic named range as every second cell of a
given row? I can define a dynamic named range but only want to include every
second cell in this named range.

I have an Actuals V Budget with months across the top in columns and actuals
in one column and budget in a second adjacent column. I am trying to plot a
graph showing monthly actual sales only (every second column) without having
to resort to manually selecting every cell.

Suggestions on how I can accomplish this would be greatly appreciated.

Thanks

Sam
 
Hi

Use an additional sheet, (you can hide it).

An example:
You have a list of values in column A on sheet MyList, with header in row1.
You need a list with values from rows 2, 4, 6, etc.

Create a sheet MyList2, with header in row1
Into A2 enter the formula
=IF(OFFSET(MyList!$A$2,(ROW()-2)*2,0)="","",OFFSET(MyList!$A$2,(ROW()-2)*2,0
))
and copy it down (as much rows as you think you need)

Create a dynamic named range
MyList2=OFFSET(MyList2!$A$2,,,COUNTIF(MyList2!$A:$A,">""")-1,1)

Hide the sheet MyList2
 
Back
Top