+ Reply to Thread
Results 1 to 2 of 2

Find word/phrase within cell & automate putting value in adjacent cell

  1. #1
    Registered User
    Join Date
    06-23-2016
    Location
    San Francisco
    MS-Off Ver
    2010
    Posts
    1

    Find word/phrase within cell & automate putting value in adjacent cell

    I am trying to automate filling of adjacent cells with dollar amounts when a certain key is typed in. A1 = Screening, B1 =$5,000.00 but for many keys.

    I used this code: =IF(ISERROR(SEARCH("*Screening*",F3,3)),IF(ISERROR(SEARCH("*Week 1*",F3,3)),IF(ISERROR(SEARCH("*Week 3*",A1,1)),IF(ISERROR(SEARCH("*Week 5*",F3,3)),IF(ISERROR(SEARCH("*Week 9*",F3,3)),IF(ISERROR(SEARCH("*Week 13*",F3,3)),IF(ISERROR("*Week 16 (Pre-Surgical)*",F3,3)),IF(ISERROR(SEARCH("*Surgery*",F3,3)),IF(ISERROR(SEARCH("*Post Surgery*",F3,3)),"","$1,108.75"),"$26.88"),"$2,151.25"),"$842.50"),"$1,839.24"),"$952.20"),"$941.25"),"$941.25"),"$2608.75")

    I received this error: "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

    HELP IS MUCH APPRECIATED. Picture is attached.
    Attached Images Attached Images
    Last edited by jyeg; 06-23-2016 at 08:46 PM.

  2. #2
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2013
    Posts
    142

    Re: Find word/phrase within cell & automate putting value in adjacent cell

    So, you're wanting the contract amount to auto-populate based on the payment description?

    Your best bet will probably be to create a lookup table on a separate sheet then use the VLOOKUP formula.

    On the separate sheet, starting at row 1 list all the payment descriptions in column A. In column B list the contract amount for the payment description.

    Back on your main sheet in cell G3 type the following formula:

    =VLOOKUP(F3,Sheet2!$A1$B9,2,FALSE)

    copy the formula down

    You will have to change the Sheet2!$A1$B9 to the actual address of your lookup table range.

+ 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] Find Word And Set Cells To Equal Adjacent Cell Value if Criteria Is Met
    By ScabbyDog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 03:48 AM
  2. [SOLVED] Macro to find specific word in cell and then copy and paste the adjacent cell upwards
    By raytej82 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2014, 05:33 PM
  3. Replies: 2
    Last Post: 10-09-2014, 05:14 AM
  4. Find Value Then Put Phrase in Adjacent Cell
    By TStanwood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2013, 04:11 PM
  5. Replies: 2
    Last Post: 01-15-2013, 02:55 AM
  6. Replies: 0
    Last Post: 09-26-2012, 02:02 PM
  7. Need to find a word in a cell, copy the entire cell and two adjacent columns on same row
    By hang_sandwich in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2012, 11:16 AM

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