Hi All,
I'll start from the beginning (like all good stories go), just in case I am chasing down the wrong path.
I get a tab separated document each week with file spaces for various drives on various servers. I want to be able to show some trending for the values over a defined period (important to note) in a graph as well as provide a 2 week "summary" on changes.
I have been able to format my data so that I can easily consistently refer to a value combining server name and drive letter (recorded a macro which does all including some conditional formatting).
I have chosen to format the sheet so new data gets inserted as sheet 3 (sheet 1 being 2 week summary and sheet 2 being the populated data for the graphs (have not decided how many, but maybe 3 as there are 55 disk values to plot).
I can create the formulas to VLOOKUP values based on me typing the sheet names in manually, but want to make it so anyone can use it, so want to the formulas for 'Summary' to reference sheet 3 and 4 always.
I would like to also be able to set data values on the graph tab according to look ups in case new disks get added/re-arranged, so that data is consistent.
I've found some VBA which has been created (thanks cpearson.com) to allow me to lookup sheet values, and they return text strings, but in trying to use those VBA functions within the VLOOKUP I get #NAME errors. I have tried with and without surrounding single quotation marks (no spaces in sheet names, so no issue there), and also putting the sheet reference in it's own cell, and having VLOOKUP point to the resultant cell without change.
Is my problem in the way I am trying to go around the dynamic situation, or a restriction in the way that functions are processed in Excel?
I'd appreciate advice on where to go to resolve my problem.
Many thanks.
Geoff
Bookmarks