ignore formulas in dynamic range

S

skippy51

Ihave a graph that is dynamic. I use the following range
=OFFSET(sheet1!$A$1,COUNTA(sheet1!$A:$A)-1,0,-MIN(chtlen,COUNTA(sheet1!$A:$A)-1),1)
If I have a formula in that column $A:$A it counts that also. How do I
ignore the cells with formula's in them so that the range does not change
until the formula produces a number?
 
B

Bernie Deitrick

Maybe something like

=OFFSET(Sheet1!$A$1,COUNTIF(Sheet1!$A2:$A10,">0"),0,-MIN(chtlen,COUNTIF(Sheet1!$A2:$A10,">0")),1)

Depends on what numbers your formulas should produce...

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Range for multiple columns 7
Named Range maker code 9
hLookup dynamic range 2
Drop Down with dynamic named range offsheet source list 14
Is it possible to create a dynamic range in CONTROL TOOLBOX (form) 7
Dynamic range using partial column? 1
sum at 3 rows below dynamic range 2
Do I really need OFFSET? 3

Top