Greetings. Any suggestions (no V

[buitenhek]

Greetings.

Any suggestions (no VBA though):
1.I have workbook where up to 30 inputs (names) are entered
2.I chart the names with % worked in a bar graph
3.If only 5 names are entered I get a graph with the 5 names/% with 25 null name ranges.
4.Can I determine the number of entries and set up an array to only chart those entered names?
Thank you.

Regards
Peter Buitenhek
Profit Developer.com

 

[SkipVought]

Hi,

You can define ranges dynamically using a number of techniques including defining a dynamic range name in Insert/Name/Define with the OFFSET worksheet function.

Then use THAT range name in the Chart Data Source name(s) where, if for instance the defined name were rDataRange on Sheet1 then

=Sheet1!rDataRange

faq68-1331 How can I rename a table as it changes size



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[luceze]

Skip,
Concerning your FAQ, If you only reference the first cell in your table will the code always set range names for the entire table? I have always used the entire table name when performing this function.

Eric

 

[SkipVought]

You would use something like this to define a table

=OFFSET(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),CountA(Sheet1!$1:$1))

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[buitenhek]

Dear Skip & Eric
Over my head.

Example
1 mary 50%
2 joe 75%
3 fred 100%
4 eve 85%
5 sam 50%
6
7 roger 25%
8
9
10 ty 60%
11
12
13
14
15
16 will 50%
17
18
If the Name or % is blank it is a null ("&quot value (ie =if(b2="","",b2)
30 entries are the maximum number of names with associated percentages.
I want 8 bar lines on that sheet not 30 with only 8 filled in.
I set up the Dynamic Name and offset but when I plug the formula into the Chart range it gives me no results.
Again thank you for the first reply.


Regards
Peter Buitenhek
Profit Developer.com

 

[SkipVought]

BIG No, No!

Blank Lines!

A data table needs to be contiguous!

Why do you have empty rows????

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

send me your workbook

skip @ theofficeexperts.com

removing the spaces

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884