+ Reply to Thread
Results 1 to 7 of 7

going a step beyond index-match function

  1. #1
    Registered User
    Join Date
    08-27-2020
    Location
    Savannah, GA
    MS-Off Ver
    2016
    Posts
    3

    going a step beyond index-match function

    Hello,
    I hope I am going about the issue I have in the correct way, but if someone sees a simpler way to complete my task I welcome any advice.

    I am making an inventory spreadsheet in which I have a weekly inventory that is performed every Monday, in which I want all weeks to be on one page:
    allonepage.PNG
    I have another sheet, which I will call the calculation page, in which i want to be able to do many functions and calculations automatically that tells me weekly usage, minimum reorder dates, etc. What I need help with is making the calculation page automatically update with the current weeks data. So far to do this I have gotten to the point where I use the "=today()" function in the E1 cell, and the function "=E1-WEEKDAY(E1,2)+1" to get the monday of the current week displayed.
    Since inventory is calculated every monday, i then use a index-match function "=CELL("address",INDEX('Weekly Inventory'!B1:U1,MATCH(Sheet1!D1,'Weekly Inventory'!B1:U1,0)))" to identify the cell from the weekly inventory page on the calculation page, i think this is a good step towards the final goal i want of displaying all of the data in the column that comes below that identified cell. For clarification i am including a picture of the calculation page:
    calculationpage.PNG
    basically, I want the data in B2 on down to automatically populate with a calculation of the difference between the current week and the previous week from the data in the weekly inventory page. I have already manually populated that data to show what the correct numbers would look like, but I feel like I could just add something to my index-match function to plug into those cells and make it happen. Please let me know if any of this is unclear, and I will clarify as best I can.

    TIA

    -Adam
    Attached Files Attached Files
    Last edited by chemicalnerd37; 08-27-2020 at 09:58 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: going a step beyond index-match function

    We can't work with a picture of your file, so it would help if you attached a sample Excel workbook instead - instructions about how to do this are given in the yellow heading at the top of the screen.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-27-2020
    Location
    Savannah, GA
    MS-Off Ver
    2016
    Posts
    3

    Re: going a step beyond index-match function

    I have attached the spreadsheet, thanks!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    Re: going a step beyond index-match function

    Sadly, the forum is having issues downloading files today. However, in Sheet1, try something like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where B2:U200 is your actual inventory range, the first index finds the matching number for item and week, second INDEX finds matching number from previous week (Same exact formula except it's D1-7).
    Questions?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    08-27-2020
    Location
    Savannah, GA
    MS-Off Ver
    2016
    Posts
    3

    Re: going a step beyond index-match function

    I apologize for my ignorance, but I am a bit of a novice at using these formulas, as evidenced by the fact that I am stuck on this problem. Should I be changing the values for the cells depending on where I place the formula you posted? I currently get an error message when I try to copy and paste that formula.


    Maybe I am going about this wrong...If I use a formula to display the contents of a specific cell, but I want to use that cell as a reference and display the contents of a cell below it, is that an easy task to perform?
    Last edited by chemicalnerd37; 08-27-2020 at 11:55 AM.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    Re: going a step beyond index-match function

    That formula would go in B2 of sheet1 (based on your post) and you'd copy it down. It looks for the intersection of A2 (the item) and B1 (the week date) - 7 and then subtracts that from the current week number from it.
    What sort of error do you get?
    Is the inventory sheet name correct in my formula?
    Try entering the formula in snippets, for example
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or each of the MATCH functions to see which gives an error. You could also do "Evaluate Formula" on the formula tab to watch Excel step through the calculations.

    PS. Someone said that xls files are opening up okay so if you want to save your example file as an xls and then upload that, I will see if I can download it.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: going a step beyond index-match function

    Try in B3:

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. INDEX MATCH Sum values with multiple results and two step process
    By MPF2020 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2020, 01:03 PM
  2. How to apply an Index.Match.Match function to all entries in a Listbox on a User Form
    By jason.drozd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-25-2018, 01:54 AM
  3. [SOLVED] Need INDEX MATCH macro to loop step 6
    By SCDE in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-26-2014, 10:48 PM
  4. Index Match.. One step away.
    By Kevin Stewart in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2013, 04:12 PM
  5. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  6. Taking an Index Match Function one step further!
    By Ben Morton in forum Excel General
    Replies: 2
    Last Post: 04-08-2009, 07:17 AM
  7. Index and Match - the next step
    By MoonWeazel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-27-2005, 01:05 PM

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