Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Dynamic number of columns where value is greatert than 0

Hi,
I'm trying to name a dynamic range using Offset.

=OFFSET(Cumulative!$C$4,0,0,COUNTA(Cumulative!$C4:$BB4),COUNTA(Cumulative!$C$4:$BB$4))

When I test the range the "dancing ants" cover the wrong area so I've obviously not grasped the concept of the Offset function.

I also cannot get it to work at all if I add the ">0"

What I'm trying to achieve is to have the named range expand to cells that have a value greater than 0 in row 4 from column C to column BB.

If I can get my head around this, I can alter the formula for more ranges and build a dynamic chart.

I have added a flat file so you can see what I'm trying  to work with.

As always your time is appreciated
Book1.xlsx
0
Stephen Byrom
Asked:
Stephen Byrom
  • 11
  • 8
1 Solution
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Managed to get it to show the correct row now, but cannot fathom how to only expand to include cells greater than 0.

=OFFSET(Cumulative!$C$4,0,0,COUNTA(Cumulative!$C4:$C4),COUNTA(Cumulative!$C$4:$BB$4))
0
 
gowflowCommented:
try this

=Sheet1!$C$4&":"&(CELL("address",INDEX(Sheet1!4:4,MATCH(0,Sheet1!4:4,0)-1)))

Open in new window


gowflow
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Hmmmm,
"try this" ..... where?
In a cell, or in the offset formula?
I tried in the offset formula but couldn't get it to show the correct data.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Stephen ByromWarehouse/ShippingAuthor Commented:
I also tried it in the Name Manager and changed "Sheet1! to the correct name (Cumulative), but still no joy.
:(
0
 
gowflowCommented:
what do you need to achieve why a named range ? I am good in VBA but these formulas thing is not my cup of tea !!
gowflow
0
 
gowflowCommented:
where did you had first your offset formula ? in a named range ?
gowflow
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Yes.
The named range is

=OFFSET(Cumulative!$C$4,0,0,COUNTA(Cumulative!$C4:$C4),COUNTA(Cumulative!$C$4:$BB$4))

C4 always has a value greater than 0, but the columns grow as weeks are added, so I am trying to get the named range to expand to only cells in row 4 where the value is greater than 0
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
I was thinking that maybe I could put a formula in an unused cell to return the cell address of the last cell in row 4 where the value is greater than 0 and refer to that in the named range "Offset" clause, but I can't get it to work.
0
 
gowflowCommented:
ok if this is what you want then do this
=CELL("address",INDEX(Sheet1!4:4,MATCH(0,Sheet1!4:4,0)-1))

this will give you the last cell that contain non zero in row 4 starting from C4

gowflow
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks,
I have entered that formula into an empty cell ( T1 ), and tried to refer to that cell in the offset formula of the named range,
=OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C),COUNTA(Sheet1!$C$4 & ":T1 &"))

But I can't seem to refer to the cell address that T1 shows, (incidently currently I4)
0
 
gowflowCommented:
pls post what you did as the file you posted did not have the offset formula so I was lost in what your trying to achieve. I usually solve things quickly !!!
gowflow
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Ok,
Thanks for your time.
I have attached the workbook
WorkInProg.xlsm
WorkInProg.xlsm
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Sorry I forgot it was protected. I have added an unprotected one
0
 
gowflowCommented:
ok fine you posted that file but where is the OFFSET ???
what are you trying to achieve ? and why ? pls explain in plain English I am getting grrrrr

gowflow
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Hmmm.
I'm trying to do what I said earlier at http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28371615.html#a39879408.
The Offset is in the name manager? And its called Total509Builds?
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
I now have this as the named range...
=OFFSET(Toyota!$C$4,0,(Toyota!T1),COUNTA(Toyota!$C4:$C4),COUNTA(Toyota!$C$4:$BB$4))

on the sheet ("Toyota") at cell T1, is the formula to get the cell address of the last cell in row 4 which is greater than 0.... "=CELL("address",INDEX(4:4,MATCH(0,4:4,0)-1))" , which returns the value $I$4.

I still can't get the offset range to show C4 to the variable cell address value of T1
0
 
gowflowCommented:
Here it is, I finally got it !!!
=Toyota!$C$4:INDEX(Toyota!$4:$4,,MATCH(0,Toyota!$4:$4,0)-1)

I create it in your last file and updated both
Total509Builds  I put its SUM in Cell G1
Total519Builds  I put its SUM in Cell H1

Just put any value in any 0 column in row 4 or row 21 and see how it automatically update G1 and H1.

Enjoy
gowflow
WorkInProg.xlsm
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for your time.
0
 
gowflowCommented:
Tks glad we got this one sorted out as frankly I am not into formulas at all and feel I did a breakthrough, I thank you for this !!! wow

gowflow
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now