PDA

View Full Version : Solved: Dynamic range table



white_flag
02-28-2011, 08:27 AM
Hello I have problem to define dynamic name ranges via OFFSET
I have an table that will be tab1 and it is defined like this:



=OFFSET(Sheet1!$B$3;0;0;COUNTA(Sheet1!$A:$A);COUNTA(Sheet1!$A:$A))


the table start from cell B3
but it is not going (and I do not why)

please look in attachment

Bob Phillips
02-28-2011, 08:48 AM
You have two tables in the sheet, so what is it referring to?

white_flag
02-28-2011, 09:02 AM
to the first one

Bob Phillips
02-28-2011, 09:19 AM
Try

=OFFSET(Sheet1!$B$3,0,0,MIN(IF(Sheet1!$B$4:$B$1000="",ROW(Sheet1!$B$4:$B$1000)))-3,COUNTA(Sheet1!$3:$3)+1)

white_flag
02-28-2011, 09:41 AM
I will try it later. Now I have to go to school

white_flag
03-01-2011, 12:44 AM
morning,
thank you, It is going very well for the first table. One question:

It is not possible to put an formula to fit all tables (different rows and columns) just the start from were the table start?

Bob Phillips
03-01-2011, 01:50 AM
Doesn't that formula, suitably adjusted, do just that?

white_flag
03-01-2011, 04:26 AM
please look in attachment

Bob Phillips
03-01-2011, 04:41 AM
I've already seen that. what am I looking for?

white_flag
03-01-2011, 05:23 AM
tab2



=OFFSET(Sheet1!$B$44;0;0;MIN(IF(Sheet1!$B$4:$B$1000="";ROW(Sheet1!$B$4:$B$1000)))-3;COUNTA(Sheet1!$44:$44)+1)
? like this it is not going

Bob Phillips
03-01-2011, 05:42 AM
You need to start all references at row 44, and probably need to remove prvious row counts on a count item.

white_flag
03-01-2011, 05:59 AM
an general formula to fit different type of tables, just to put the starting point of table ..and the rest to be automatically..

but I ques, it is not going

Bob Phillips
03-01-2011, 06:30 AM
Yes, of course it can, but the fact that the top left cell in table #1 is blank, and the one in table #2 is not unnecessarily complicates the formula.

white_flag
03-01-2011, 06:56 AM
but if the cells are blanks? (Top left)

Bob Phillips
03-01-2011, 07:55 AM
Okay, assuming that then use



=OFFSET($B$3;0;0;MIN(IF($B$4:$B$1000="";ROW($B$4:$B$1000)))-ROW($B$3);COUNTA($3:$3)+1)


Note that for the second table you change the $3 to $44, and the $4 to $45.

white_flag
03-01-2011, 08:38 AM
with a bit of modification, it is going, also for the following tables.
Bob .. thank you :)