Create more than one dynamic range in a worksheet

[CLoos]

Is there a way to do this. I have fooled with this unsuccessfully. I have tried a few versions and I can't seem to make it column specific. For example I have data in B:N and Q:W and X:AC. Do you have to have different sheets?

=OFFSET('Compiled Data for Pivots'!$B$2,0,0,COUNTA('Compiled Data for Pivots'!$B:$N),1)

 

[NARAYANK991]

Hi ,

Can you explain what you want to do ?

I am not able to figure out how your posted formula is related to what you describe.

Narayan

 

[Hui]

As Narayan has pointed out it is unclear what you are trying to do

can you describe you problem and what you are trying to achieve?
A sample file may help

If you want to choose different ranges for a formula based on a cell value
I suspect you need something like:
CHOOSE(B2,$B:$N,$Q:$W,$X:$AC)

This will choose B:N if B2=1, Q:W if B2 = 2 and X:AC if B2 =3

you could use that inside a VLookup etc
=Vlookup(Value, CHOOSE(B2,$B:$N,$Q:$W,$X:$AC), Column No)

 

[CLoos]

Is there a way to do this. I have fooled with this unsuccessfully. I have tried a few versions and I can't seem to make it column specific. For example I have data in B:N and Q:W and X:AC. Do you have to have different sheets?

=OFFSET('Compiled Data for Pivots'!$B$2,0,0,COUNTA('Compiled Data for Pivots'!$B:$N),1)

 

[CLoos]

Here is a clumsy example but I am trying to create a named dynamic range for 3+ arrays of data on the same worksheet. For example, I pasted data into A:M, O:AA, AC:AO. I want to create 3 separate dynamic named ranges for each mentioned range. The problem is I keep getting these unusual results and the last time I was told it was a circular formula. I was wondering if having dynamic ranges on the same worksheet was causing the issues. The formula I posted was only one of many I attempted.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan