+ Reply to Thread
Results 1 to 11 of 11

VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    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

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Hi Geoff,

    welcome to the forum.
    Nice explanation but I believe a sample workbook will add interest to it, please upload. thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    03-18-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    I've attached the work book.

    I really means nothing as it is a bunch of numbers. Some of the data is obfuscicated, but I left enough realism in there to see what I am doing.

    I hope it helps.
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Hi Geoff,

    I found lot of sheets and numbers.. also more than one query in your initial post. Can we take it one by one (step by step) ?
    To begin with what you need in first place ?

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Hi Dili,

    Firstly thanks with your assistance on this.

    Quote Originally Posted by dilipandey View Post
    Hi Geoff,
    To begin with what you need in first place ?
    To make it as simple as possible for anyone else wanting to update the report with new data.

    I am pretty sure I can do most things except the dynamic update of the summary and graph data. I also want the graphs themselves to stay on a static 3 month range and not move as new data is inserted at the beginning.

    Geoff

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Okay.. just now I provided a similar solution, see below thread:-

    http://www.excelforum.com/excel-char...r-a-chart.html


    I hope you can get an idea from above, and if stuck, upload your workbook with your progress made. Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Registered User
    Join Date
    03-18-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Quote Originally Posted by dilipandey View Post
    Okay.. just now I provided a similar solution, see below thread:-

    http://www.excelforum.com/excel-char...r-a-chart.html


    I hope you can get an idea from above, and if stuck, upload your workbook with your progress made. Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    I've had time to have a look at your sheet, and see how you have used name manager to define the region in the sheet. That certainly helps the dynamic graphing part of my query.

    I'll work on that and see how things go. I will still need to sort out the summary graph data updating the vlookup values in the summary to look at the relative sheet positions as each week has its data updated.

    Geoff

    edit: hmm I see how you are getting column data, but I need row data. I need to figure out how to address it.
    Last edited by Syncr0s; 03-21-2013 at 08:58 PM.

  8. #8
    Registered User
    Join Date
    03-18-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Quote Originally Posted by Syncr0s View Post
    edit: hmm I see how you are getting column data, but I need row data. I need to figure out how to address it.
    AARRGGGHHHHHH!!

    So I've figured out the row thing for the name definitions, so all good, but why is Excel so inconsistent??

    If I import the functions located at http://www.cpearson.com/excel/WorksheetFunctions.aspx, and use the SheetNameOffset function, then =COUNTA(SheetNameOffset(1)*A:A) doesn't work, and neither will VLOOKUP using the same function as part of the look up range. Specifying the sheet name manually (i.e. =COUNTA('18.03.2013'!$A:$A) does work though.

    Is this because Excel's built in functions won't look at user defined functions when making calculations, or what is the cause?

    This is REALLY annoying me as I can use this ability a lot with my automated updates for summary and detection of volume addition or removal.

    Geoff

    edit: I should note that if I type the tab name into a cell and try to reference the cell as tab name in the function the same problem occurs i.e. =COUNTA('G67'!$A:$A), or =COUNTA(G67!$A:$A) or =COUNTA($G$67!$A:$A)
    Last edited by Syncr0s; 03-22-2013 at 12:59 AM.

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Hi Syncr0s,

    Upload your workbook along with your expected results..


    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Hi Syncr0s,

    Upload your workbook along with your expected results..


    Regards,
    DILIPandey
    <click on below * if this helps>

  11. #11
    Registered User
    Join Date
    03-18-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VLOOKUP Based on relative sheets (weekly added data) (Excel 2007)

    Quote Originally Posted by dilipandey View Post
    Hi Syncr0s,

    Upload your workbook along with your expected results..


    Regards,
    DILIPandey
    <click on below * if this helps>
    It is the same workbook I have already uploaded.

    The worksheet 11.03.2013 has filtered column C, and I want to use a function on Summary tab with the formula =IF(COUNT(A:A)<SUBTOTAL(3,SheetNameOffset(1)!C:C),"New Volume",""), then I get the "Your formula contains an error" message. Using =IF(SUBTOTAL(3,A:A)<SUBTOTAL(3,SheetNameOffset(1)!$C:$C),"New Volume","") gives the same error. Putting =SheetNameOffset(1) in cell B59, and reference it in =IF(COUNT(A:A)<SUBTOTAL(3,'B59'!C:C),"New Volume",""), I get "New Volume" as a result, even though the count values are the same.

    I am wondering whether the VLOOKUP I am trying to formulate has the same issue in dealing with the nested function of SheetNameOffset.

    SheetNameOffset comes from the cpearson.com site I linked above.

    Geoff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1