Create Named Ranges from Cells

[pto160]

I'm trying to create Named excel ranges for one column with different row ranges based on the month in my case. Is there any way to do this from a cell range selection.
So in my case Column B is where are the names of the ranges, and column C is the ranges itself. It takes a long time manually to do this.

Sheet1

	B	C
Expense_Jan_13	=Sheet2!A2:A10
Expense_Feb_13	=Sheet2!A11:A20
Expense_Mar_13	=Sheet2!A21:A30

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 107px"> <col style="WIDTH: 110px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[tom_blamire]

why not just give column B a named range and then the same for column C. Use either INDEX & MATCH or VLOOKUP to return corresponding value. Alternatively, place the data in a table (Ctrl+T) and it will do it automatically.

 

[pto160]

I think I know what you're getting at. Yes, thinking about it I would like to use Dynamic Named ranges since I don't have to keep adjusting the ranges. The formula I would be looking for would be the first and last row of the month for the range I'm looking for like July. My data is sorted by date.
Sheet1

	A	B
Jul_A
Jul_A
Jul_A
Aug_A
Aug_A
Aug_A
Aug_A

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]50[/TD]

</tbody>

So Range1=$B2:$B4. A formula would see the first row Jul_A occurs is row 2 and the last row is row 4
Range2=$B$5:$B$B8

I'm basically using Column A to figure out the row ranges.

How do you do this?
Excel tables to the web >> Excel Jeanie HTML 4

 

[tom_blamire]

What are you trying to acheive with the named ranges? If you put it in table format you can just use a SUMIF function to work out the totals of the months.

 

[pto160]

I'm using index and match with the ranges to get a specific match. I want the range to be the month in question I'm trying to get the match for. Speadsheet is very big so named ranges is essential based on month.

 

[tom_blamire]

try using Ctrl+Shift+F3

 

[pto160]

I have the formula for the row portion.
=

=small(ifA2:A8="Jul_A",row(A2:A8),1)

That's for the first row or the month in question.
For the last row.

=large(ifA2:A8="Jul_A",row(A2:A8),1)

How do you get the column B in the formula so Excel will read B2:B5 for the range Jul_A?
Can anyone help?

 

[tom_blamire]

Personally, i wouldnt fix the formula with "Jul_A" as you will have to change it every time. Make it reference to a cell and fix that cell - so much easier!
Nonetheless, assuming your first formula is correct, change your second to :

=large(if(A2:A8="Jul_A",row(b2:b8),1)