Results 1 to 4 of 4

part of array formula based on data change in another column?

Threaded View

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    part of array formula based on data change in another column?

    I have the following two worksheets and am trying to retrieve the ItemID in the "New" worksheet from the "Item-Group" worksheet.

    The first few items in column C on the "New" worksheet tab are correct, but I want to fill in the rest of the table (which is far longer in practice).

    Column C's formula is
    =INDEX('Item-Group'!$A$2:$F$126,SMALL(IF(Groups=B2,ROW(Groups)-1),ROW(A1)),1)
    , entered as an array formula, and then copied down the column. The problem is, for C4, the formula has ROW(A3), but since that's where group b starts in the column, I want it to go back and restart at ROW(A1). Is there a nice way to do this?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need Help - Automatic website navigation page change based on column data change
    By cbatten01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2015, 08:00 PM
  2. Can I make the row lookup array/range part in an array formula variable?
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:06 AM
  3. You cannot change part of an array
    By Paul H765 in forum Excel General
    Replies: 5
    Last Post: 09-27-2012, 03:33 PM
  4. Replies: 4
    Last Post: 09-05-2012, 01:50 PM
  5. Cannot change part of an array?
    By money n da sank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2008, 01:12 PM
  6. How can I change a # in an array formula for a whole column?
    By gswegan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 PM
  7. warning: cannot change part of an array. how do I by pass this?
    By Gwyneth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-08-2005, 07:20 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