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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.