Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Not sure what function I need to use.

    Hi all,
    I'm sure this is a very simple one but with my limited experience I'm unable to get it by my usual trial and error!
    Basically I want to reference a cell by the corresponding date.
    For example, I have a database that generates a report of figures over a month. It puts the figures in a table with 1,2,3,4,etc as the header which represents the day of the month.
    Those dates might be going from D2 to AH2. Then I have 22 rows of information (different departments) for each day.
    I want to use this to produce a daily chart I can use in the morning meeting for each areas performance from the previous day.
    So on the 13th, I want to grab the figure for each area for the 12th.
    I have already set up an auto updating workday cell on my daily chart and a seperate cell to only show the day number which I hoped to use as a reference for the month report. (reference A)

    So when I want information for AREA G (row11) for the 12th (column o, where O2 is showing 12) I've been trying write something that says to check between D2 to AH2 for a figure that is the same as reference A. Once it finds an equal value (O2) then display the information from the corresponding column in row11 (O11).

    Make sense?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =MATCH(reference A,D2:AH2,0)
    will return the relative position of reference A in the range D2:AH2. You can then use that in conjunction with INDEX to get the value you need:
    =INDEX($D$3:$AH$24,11,match_value_from_first_Step)

    will return the relevant value in the 11th row of the lookup table (actually row 13).
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    Pedsy (2015-05-13)

  4. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks rory. That nailed it.
    I don't understand the witchcraft that makes the 2 formulas work together, but it they do, so thank you!

  5. #4
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    This is working really well for me at the moment.
    I have a dropdown list consisting of a cell using the previous work day formula followed by a column of cells numbered 1 to 31. By default it is set on previous work day and automatically updates the information to that each day. Or I can select any other previous day in the dropdown and my report grabs the info for that particular day based on the formulas we used above.

    I have another request though:
    On this report I also show cumulative calculations (month to date) which look like this -
    =SUM(SUM('[Temp.xls]Notes Data'!$BR$8:$CV$8)/SUM('[Temp.xls]Notes Data'!$AK$8:$BO$8)*100)
    This is independent of the stuff we set up earlier. So this still shows information for the entire month regardless of what date I am looking at via the drop down.
    How can I get this to show accumulated information only up to the date I have selected? So say I select 15 in the dropdown I only want it to show (BR8:CF8/AK8:AY8)*100.
    I can't quite get my head around it.

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Pedsy

    ..it would be easier to help if we had a sample file.
    Change any sensitive data as required.

    zeddy

  7. #6
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sure zeddy. It was a little hard to put into words :P

    The sample is what I print each day for our morning meeting.
    The Notes Data tab is normally a separate file which I generate from a Lotus Notes database. I generate a new one each day and save over the top of the old one so I have the latest data.
    My dropdown list is in AB4.
    The MATCH formula from rory is in AB6.
    The INDEX formulas are in AE,AF,AI,AJ.
    You can see that the results in those columns change when you change the number in the dropdown.


    The data in columns Q and T are cumulative calculations from from the 'Notes Data' tab.
    I would like these to also change when I change the number in the dropdown.
    for example, Q7 is using '=SUM(SUM('Notes Data'!$BR$8:$CV$8)/SUM('Notes Data'!$AK$8:$BO$8)*100)' which takes in the full month.
    I would like it to calculate only up to the day that I have selected in the drop down. So if I select 5 it would only use BR8:BV8 and AK8:AO8.
    I remember stumbling on this happening when I was trying to sort out the MATCH/INDEX stuff before I came here for help, but now I can't remember what it was that did it
    Attached Files Attached Files

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Pedsy

    see attached file.

    I added a 'clicker' button to change the day value (I find it easier than the dropdown selection)

    I simplified the formulas on sheet [Sample] for column [Q] CUMMULATIVE MTD, and column [T] LOST TIME
    I did the calculations on sheet [Notes Data]

    Click the clicker and see the results.

    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    Pedsy (2015-05-20)

  10. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just as an FYI, you would make the workbook a lot easier to maintain if the Line names in column C of the Sample sheet matched those on the Notes Data sheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi rory

    ..it certainly would.
    If the names were the same on both sheets, we could use a MATCH and INDEX method to fetch the required values, instead of having to specify the particular row for each name.

    zeddy

  12. #10
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Very nice zeddy!
    I have never seen a clicker before. Is that easy to add?
    It is doing exactly as I wanted. The only issue I will have is that Notes Data is normally a separate file which is generated via a Lotus Notes Database and is written over each day, so I can't add any calculations to that sheet.
    I could probably get IT to add it as part of the generated file. I did get them to add the seperate morning and arvo shift tables so I could do what I wanted at the start of this thread, but I think I would rather keep it seperate.
    I'll have to do a bit of googling on how that SUM(OFFSET( formula works.
    I agree with the common line names but the Notes Data uses information that is organised using process codes, where as I use the common name for the areas as not everyone would understand that, for example, F3XX is the Meters Line.
    I'm also going to have a look at a way of getting the date cell to also change to correspond with the date I have selected with the dropdown/clicker.

    UPDATE:
    I have transfered those formulas to the 'sample' sheet and just changed the first cell reference to the external file.
    Works great.

    UPDATE 2:
    Hmm.
    After closing and reopening those files the formulas are broken:
    =SUM(OFFSET('drive:\dir\dir\dir\dir\dir\[Temp.xls]Notes Data'!$AJ$17,0,1,1,$AB$4)) *changed path name for privacy.
    Does this type of formula not like refering to an external source?

    A very clunky solution I am considering is copying zeddy's version of the 'Notes Data' tab into my main file and refering all the cells to the same cells in the generated 'Notes Data' file. Then that will auto refresh as I save new versions of the generated file each day and I can use the formulas the way zeddy set them up.

    UPDATE 3:
    I've figured out that the formula above only works when I have the reference file open first. Not ideal. I think I will still try my solution above.

    On a side note, why is it some formulas have no problem referring to data from closed files but others don't?
    Also why do some formulas update on the fly (I have other charts that refer to other information from that generated file that I deleted from the sample. I can change a value in the generated file and the chart changes instantly) yet others, like my day shift/arvo shift INDEX formulas in sample require me to shut and reopen the file for updated values to appear?

    *I deleted my triple post and put it all in this one.
    Last edited by Pedsy; 2015-05-20 at 20:45.

  13. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    All formulas should update unless you have manual calculation turned on.

    No function that requires a range (i.e. won't accept an array) will work if the source workbook is closed. That includes OFFSET, INDIRECT, SUMIF(S), COUNTIF(S).
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Pedsy

    A clicker is very easy to add. You use the Developer tab to insert the clicker. If you don't have the Developer tab showing in your top-panel Ribbon, tell us which Excel version you have and we can tell you how to show it.

    In the attached file, the [Notes Data] sheet can be updated separately i.e. 'new data' can be pasted onto the entire sheet. This will overwrite the formulas placed there in previous version posted.
    So now, you just click the button on sheet [Sample] to replace the missing formulas. These are copied from the sheet [Parameters]. In the attached version, the formulas haven't yet been copied. You can tell this by looking at the 0.0 values in column [Q]. Click the button to add the required formulas.

    I have defined two names in this sample file, and linked the date to the clicker value.

    zeddy
    Attached Files Attached Files

  15. The Following User Says Thank You to zeddy For This Useful Post:

    Pedsy (2015-05-26)

  16. #13
    New Lounger
    Join Date
    Apr 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Just checking back in to let you know that these changes have really helped me out. Thanks again guys!
    zeddy, yes I did have the Developer tab. I added it not that long ago when I wanted to try to make a few macros. I never noticed the 'Insert' dropdown though. Some handy little toys in there!

Posting Permissions

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