Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update a dynamic text box in excel chart automatically

Posted on 2012-04-10
9
Medium Priority
?
485 Views
Last Modified: 2012-04-11
Hi,

I have a  chart in excel 2007 that a user updates almost daily. The chart gets updated appropriately using a named range that contains the offset function (=OFFSET('T'!$U$17,15,0,1,COUNTA('T'!$U$17:$EA$17)).  I would like to improve this chart by adding a text box that updates with the last value in row that is based on a calculation. I can link the text just fine, but the next day when new data is entered, the text box does not get automatically updated.

Thanks,

Bruce
0
Comment
Question by:Diaphanosoma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37829634
I don't think you need VBA for this.  Does that text box you create have a formula referencing a cell in it, and if you update that cell, the textbox gets updated?

Is it that you need a formula for the last value in a column?  Are these values text or numeric?

The following will return the last value in a given column, say, for column A:

=LOOKUP(2,1/(A:A<>""),A:A)

See attached demonstration with chart/text-box

Dave
chartWithTextBox-r1.xls
0
 
LVL 1

Author Comment

by:Diaphanosoma
ID: 37829792
I'm going to modify to work on my data, but I don't understand the formula at all! I ran it through the formula evaluator and that didn't help either.
Why are you looking for a  value of 2?
Why is there division in the lookup vector?
Better yet can you explain how and why the formula works?

Thanks,

Bruce
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37829864
I'm happy to do so.  There are actually several different formulas you could use.

E.g., find the last number in a column:

=INDEX(A:A,MATCH(99^99,A:A))

find the last text in a column

=INDEX(A:A,MATCH(rept("z"),A:A))

find the last value (number or text) could be a complicated version of the above, or we can use LOOKUP.  Let me explain LOOKUP by talking about the match and lookup (including vlookup) functions in general:

1.  They all THINK your data is sorted - this is important
2.  MATCH/VLOOKUP have options for exact match, or NEXT HIGHEST value (re: the largest value being looked for that is less than or equal to what you're looking for).
3.  The Lookup function also find either the match or next highest value.


So, in the case of Lookup(2,1/(A:A<>""),A:A) what is going on?

The first parameter is the search parameter,
The second parameter is what is being searched.

These two parameters act together to create an index which goes against the last parameter.  Kind of how INDEX/MATCH works but all in one formula.

Let's talk about the 1/(A:A<>"").  This returns an array of 1/(TRUE's and FALSEs) which returns an array of 1's and #DIV!0's.  Note the maximum valid value of 1/(A:A<>"") is 1.  You can combine this with other arrays to make multiple criteria work.

So, if we do a Lookup(2,1/(A:A<>""),A:A) we would get the LAST instance of a value that is non blank in column A.  Why?  Well, Lookup thinks the data is sorted, so it starts at the bottom of the range and works its way up, looking for the maximum value (because we're using 2 and 1 is the only valid value) until it finds it.  As soon as it finds a valid value that is less than or equal to 2, it uses that.  That's why it returns the last value.

Hope this helps.

Dave
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!

 
LVL 42

Expert Comment

by:dlmille
ID: 37829888
PS - if you know your data is numeric, you can use INDEX/MATCH as above, or the Lookup:

=LOOKUP(99^99,A:A,A:A)  99^99 a very big number, so would find the next highest - thinking its sorted, would return the next valid number

and if you know your data is text,  you can use:

=LOOKUP(REPT("z",20),A:A,A:A)  rept("z",20) a very big text value, so would find the next highest - thinking its sorted, would return the next valid text value

Why did I use =Lookup(2,1/(A:A<>""),A:A)  ???

As I didn't know if it was text or numeric, I just gave you a lookup that used criteria A:A<>"" so I KNEW the result would be a correct one.

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37832038
>>"Well, Lookup thinks the data is sorted, so it starts at the bottom of the range and works its way up"

It's a binary search, I think you'll find, which is why it's much faster than exact matches.
0
 
LVL 1

Author Closing Comment

by:Diaphanosoma
ID: 37832346
Perfect solution and explaination.

Thanks so much.

Bruce
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37833882
>>It's a binary search, I think you'll find, which is why it's much faster than exact matches.

That's good to know, but the reason it finds the last cell (meeting the criteria of a very large number/text stream that doesn't exist in the data) is due to the fact it assumes the data is sorted
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37834275
Correct - I wasn't disputing that part (otherwise a binary search wouldn't work for LOOKUP). :)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37834283
Got it!

Dave
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question