Graph

[Davycc]

Ok

I have tried but can't work it out. I have several worksheets in the same book with a column of different values (see attached a simple example)
The highest value is automatically highlighted. Is it possible to have a graph, say on sheet 8, which plots the highest values automatically. At present I'm doing a new graph manually every so often.

As usual I know you guys will come up trumph.

Davycc

[MrShorty]

The first step will be to bring each of the Max values into sheet8. A formula such as =MAX(sheet1!$D$2:$D$22) (note the use of absolute references so the range reference doesn't change as it is copied) will bring in the max value from sheet1. You can then copy this formula, manually change the "sheet1" to "sheet2" (and 3 and 4 and so on for as many copies as you need), which will bring each max value into sheet8. Generating the graph from there should be straighforward.

If you don't want to manually change the "sheet1" part of the reference, you can use the INDIRECT() function to build the reference as a text string http://office.microsoft.com/en-us/ma...778.aspx?CTT=1
Something in A1 like =max(INDIRECT("sheet" & row() & "!D2:D22")) copied down will create the desired range of max values from each sheet.

[Davycc]

hi thanks

When I do this for the example it works fine. But when I try it on my actual workbook it doesn't. I am changing the sheet names to that of the sheets I'm using and also the cells I wish to calc ie from d2:d22 to my actual h7:h27 it keeps asking me to update values in my sheet and brings up a save window.

[MrShorty]

Are you using the INDIRECT() function or not?

It sounds like Excel is not recognizing the reference string you are using for the MAX() function, so, if we can just figure out what your actual reference string should look like, we can get it to work. Try this:

1) In one of your result cells, type "=MAX( [use the mouse to navigate to the desired sheet, point and select the desired range] )" then enter. Excel should automatically build the correct reference string for the MAX() function.
2) Once we have a working example of the reference string, you can decide if you want to use the INDIRECT() function to concatenate a suitable reference string for all instances of the MAX() function, or if you want to copy and paste and edit the reference as Excel creates it.

[FDibbins]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)