Results 1 to 3 of 3

Thread: adjusting dynamic range formula

  1. #1
    Neophyte kartay's Avatar
    Join Date
    Jan 2015
    Location
    Iowa
    Posts
    3
    Articles
    0

    adjusting dynamic range formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I'm using a formula someone else created for me and I canít modify it without creating a problem in my file. I'm confident the formula is correct, the problem is me. Here is the formula I am struggling with, it is found on the "red" tab A column.

    =IF(ISERROR(INDEX('Master list'!$A$1:$C$99,SMALL(IF('Master list'!$B$1:$B$99="red",ROW('Master list'!$B$1:$B$99)),ROW(1:1)),2)),"",INDEX('Master list'!$A$1:$C$99,SMALL(IF('Master list'!$B$1:$B$99="red",ROW('Master list'!$B$1:$B$99)),ROW(1:1)),1))

    I'm trying to dynamically update color team lists from a master list. Attached is a sample file.

    The formula only references to row 99 in the master list, but I need it to go to 5,000. So if I adjust the formula, for example, $c$99 to $c$5000, A2 changes from "Thompson, Jewel" to "Staff Member", which is wrong.

    I know it is because I really don't understand what the formula is doing - though I would really love to understand it.

    I would really appreciate help in adjusting the formula correctly and helping me understand what is going on.

    Thanks

    Kara
    Attached Files Attached Files

  2. #2
    This worked for me


    =IFERROR(INDEX('Master list'!$A$1:$C$99,SMALL(IF('Master list'!$B$1:$B$99="red",ROW('Master list'!$B$1:$B$99)),ROW(1:1)),1),"")

    array-entered of course

  3. #3
    Neophyte kartay's Avatar
    Join Date
    Jan 2015
    Location
    Iowa
    Posts
    3
    Articles
    0
    You are a wonder. It worked and I could adjust it. Thank you so much.

    Kara

Tags for this Thread

Posting Permissions

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