+ Reply to Thread
Results 1 to 7 of 7

Trouble with nested IF formula

  1. #1
    Registered User
    Join Date
    11-08-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Trouble with nested IF formula

    Hi All

    I'm in the middle of creating an audit spreadsheet that automatically populates a reference number when you select the month and person's name that you want to audit. The formula references an external worksheet to keep the source information and the "front end" section users will look at apart.

    I have separated the months out into sheets within the source workbook, and have managed to get an IF formula to work so when you select one of the months it works, however I can't get it to work for the other months in the same formula if that makes any sense.

    My formula so far is:

    =IFERROR(IF(C6="December 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]December!$A2:$G200,5,0)),"")

    C6 is where you would select the month, C4 is where you would select the person's name, and "Service1" is one of several services the person works on - I'm going to do each one separately.

    I am hoping to get the formula to look something like this, where it returns the correct reference number depending on the month/name selected however it isn't working and just returns FALSE with every month except December:

    =IFERROR(IF(C6="December 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]December!$A2:$G200,5,0)),IF(C6="November 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]November!$A2:$G200,5,0)),IF(C6="September 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]September!$A2:$G200,5,0))"")) etc.

    I'm not very experienced with Excel and I'm at a bit of a loss as to how to get it to work! I've tried taking out and adding parentheses and commas etc. but nothing works, saying I've either got too many or too few arguments in the function.

    Any ideas would be greatly appreciated!
    Last edited by 1993e; 11-08-2018 at 06:58 PM. Reason: marked as solved

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    5,988

    Re: Trouble with nested IF formula

    You should look at using the indirect function Functions.

    Untested because you did not attach a sample worksheet.

    =IFERROR(VLOOKUP(C4&"Service1",indirect("[Audits_2018.xlsx]" & C6 & "!$A2:$G200"),5,0)),"")
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    11-08-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Trouble with nested IF formula

    Many thanks for your reply, I've given your formula a go but it's coming up with a "too few arguments" error unfortunately.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    5,988

    Re: Trouble with nested IF formula

    Like I said. Untested.


    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    11-08-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Trouble with nested IF formula

    Quote Originally Posted by mehmetcik View Post
    Like I said. Untested.


    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Thank you, I have attached the spreadsheet with all other sections that aren't central to my query removed, and data sheet with reference numbers I would like to appear automatically when the name/month are entered. The data is made up with false names etc.

    Many thanks
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    5,988

    Re: Trouble with nested IF formula

    =VLOOKUP(C4&"Service 1",INDIRECT("[Data.xlsx]" & LEFT(C6,FIND(" ",C6,1)-1) & "!$A2:$D200"),4,0)

  7. #7
    Registered User
    Join Date
    11-08-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Trouble with nested IF formula

    Quote Originally Posted by mehmetcik View Post
    =VLOOKUP(C4&"Service 1",INDIRECT("[Data.xlsx]" & LEFT(C6,FIND(" ",C6,1)-1) & "!$A2:$D200"),4,0)
    Amazing, thank you so much!! Never would have figured that out myself!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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