+ Reply to Thread
Results 1 to 11 of 11

Use a Drop Down as a action point ot complete a formula

  1. #1
    Registered User
    Join Date
    01-22-2017
    Location
    Bristol,England
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Use a Drop Down as a action point ot complete a formula

    I have attached the spreadsheet.

    On The week tab, I want to use Cell A2 as a filter to complete the formulas in C16:G22 and across to column G.

    Ideally is Cell A2 has "Test", it will only show the results for the 2nd,5th,6th,8th Jan

    Also if I then change A2 to "Test 1", it will show the results on 3rd and 7th Jan.



    Any Help will be amazing
    Attached Files Attached Files
    Last edited by TrevorHE; 01-22-2017 at 10:55 AM.

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Use a Drop Down as a action point ot complete a formula

    Look attach file. Use index and two match formula, base on "A2" cell & Date criteria.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    01-22-2017
    Location
    Bristol,England
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: Use a Drop Down as a action point ot complete a formula

    Hi AVK,

    Thank you for your help thus far. I am hoping I can impose your your expertise a little more.

    In the formula you have added, can I ask where you reference the "GAK" sheet, can these be changed to be the Named Ranges e.g, GAK_Sheet , GAK_Project.

    Also is there any what that you can advise on how to carry the formula you created to display the information for Location, Start Time, Finish Time, Hours Claimed.

    Once again thanks for your help

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Use a Drop Down as a action point ot complete a formula

    you reference the "GAK" sheet, can these be changed to be the Named Ranges e.g, GAK_Sheet , GAK_Project.
    In that case you can change range in "Refer to"

    For date time formula : There are so many links available, some links.
    https://www.ablebits.com/office-addi...te-time-excel/
    http://www.myonlinetraininghub.com/e...ulation-tricks

  5. #5
    Registered User
    Join Date
    01-22-2017
    Location
    Bristol,England
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: Use a Drop Down as a action point ot complete a formula

    Hi AVK,

    I tried to change the ranges to refer to the Named Ranges I have already created, and once this is done, the formula stops working.
    This is how I have changed them

    =IFERROR(INDEX(GAK_Sheet,MATCH(1,(GAK_Project=Week!$A$2)*(GAK_Date=Week!$B14),0),3),"")

    The Named Ranges i am using in the above formula, are referencing the same part of the GAK sheet you reference to.

    Also have you got any advice to carry the formula across the other cells?

    Thanks Again

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Use a Drop Down as a action point ot complete a formula

    Formula in C14:

    =IFERROR(INDEX(GAK!$A$1:$M$36,MATCH(1,(GAK!$B:$B=Week!$A$2)*(GAK!$A:$A=Week!$B14),0),COLUMNS($A:C)),"")

    Enter with Ctrl+Shift+Enter

    Copy down and across to F

    Change A2 to "Test 1" or "Test 2"
    Last edited by JohnTopley; 01-22-2017 at 03:10 PM.

  7. #7
    Registered User
    Join Date
    01-22-2017
    Location
    Bristol,England
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: Use a Drop Down as a action point ot complete a formula

    Hi John,

    Yes these will be all the entries needed in A14

    I have also just pasted this into cell C14, and the formula shows a empty cell instead of Vacation

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Use a Drop Down as a action point ot complete a formula

    Your formula works: below modified to extract other columns

    =IFERROR(INDEX(GAK_Sheet,MATCH(1,(GAK_Project=Week!$A$2)*(GAK_Date=Week!$B14),0),COLUMNS($A:C)),"")

    Enter with Ctrl+Shift+Enter

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Use a Drop Down as a action point ot complete a formula

    See attached: AVK's file
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-22-2017
    Location
    Bristol,England
    MS-Off Ver
    MS Office 2010
    Posts
    7

    Re: Use a Drop Down as a action point ot complete a formula

    Hi John

    I feel I am doing something wrong here. I have pasted yours and the amended formula into cell C14, and I still cant get the formula to show the correct information, instead it shows empty cells.

    Can you also explain the relevance of the Columns formula in this, as it is new to me.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Use a Drop Down as a action point ot complete a formula

    The Columns simply acts as a counter.

    In AVk's formula he had 3 (referencing column C in the A:M range of INDEX) which is the sames as COLUMNS($A:C) . As you drag the formula across it becomes COLUMNS($A:D) then COLUMNS($A:E) i.e 4 then 5 so the INDEX part references the relevant column in the range $A2:$M36)

    FOR A2="TEST" there are empty cells in C15, C16 but you should get VACATION in C14.

    You will an empty cell if you do not use Ctrl+Shift+Enter: the {.....} brackets will appear round the formula if this is done correctly.

    Signing off now for the night!

+ 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. [SOLVED] Add password in a button code to complete the action
    By Immortal2014 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-26-2016, 03:06 AM
  2. Excel is waiting for another application to complete an OLE action.
    By Doruli in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2015, 06:03 AM
  3. There isn't enough memory to complete this action.
    By matrix2280 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2015, 12:51 PM
  4. There isn"t enough memory to complete this action
    By Tyso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2015, 01:31 AM
  5. Excel is waiting for another application to complete an OLE action
    By TheDude76 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2013, 01:19 PM
  6. If text found in list then complete action
    By CJ944 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2010, 08:38 AM
  7. Replies: 0
    Last Post: 05-04-2005, 12:06 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