Results 1 to 6 of 6

Thread: Updating cells on to a diferent tab

  1. #1

    Updating cells on to a diferent tab



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

    Hi, i would like to know how to Update some cells using data from another sheet on the same excel file.

    I have made an example xls and attached it.

    Basically each User 1(row) will have columns with the number of the days in a month.
    When u assign the value "OK" i a cell that is in the column [example, day 21] then the column named User 1(on the other sheet)
    will get the value "21" in its corresponding cell.

    I think you are TEST.xlsxgoing to need to see the file attached

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    In A2, enter formula:

    =INDEX('INPUT SHEET'!$B$1:$AF$1,SMALL(IF(INDEX('INPUT SHEET'!$B$2:$AF$3,MATCH(A$1,'INPUT SHEET'!$A$2:$A$3,0),0)="OK",COLUMN('INPUT SHEET'!$B$2:$AF$3)-MIN(COLUMN('INPUT SHEET'!$B$2:$AF$3))+1),ROWS(A$2:A2)))

    confirmed with CTRL+SHIFT+ENTER not just ENTER, then copy down and to next column. Format the results as General or Number.

    If you really want to show as date in January, then try:

    =DATE(2015,1,INDEX('INPUT SHEET'!$B$1:$AF$1,SMALL(IF(INDEX('INPUT SHEET'!$B$2:$AF$3,MATCH(A$1,'INPUT SHEET'!$A$2:$A$3,0),0)="OK",COLUMN('INPUT SHEET'!$B$2:$AF$3)-MIN(COLUMN('INPUT SHEET'!$B$2:$AF$3))+1),ROWS(A$2:A2))))

    confirmed with CTRL+SHIFT+ENTER not just ENTER, then copy down and to next column. Format the results as Date. (Note the year and month are hard-coded into the formula here).


  3. #3
    Thank you so much,

    it works like a charm..


  4. #4
    Hello again,

    I just wanted to ask i there is a change i can make to the 1rst formula so i would read every second line starting from the first.
    On the INPUT SHEET

    User 1 => Check the OK's
    User 1 => No action
    User 2 => Check the OK's
    User 2 => No action
    User 3 => Check the OK's
    User 3 => No action
    ....and so on.

    and then on the REPORT SHEET as it already is.

  5. #5
    really sorry to push the envelope, but how can i change the output to rows instead of columns on the REPORT SHEET, like thisTEST_rows.xlsx

  6. #6
    also can i put more users on the INPUT SHEET, cause it dosen't seem to work if i put more than 2.

    THNX

Posting Permissions

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