Excel Function Help

Discussion in 'General Programming Support' started by Mahucharn, Jun 3, 2015.

  1. Mahucharn

    Mahucharn I wear a fez now, fezzes are cool. Staff Member

    Ratings:
    +179 / 0 / -0
    Hi Everyone!

    I'm not dead (yet)! I've been working a lot lately. I wasn't sure if this should have gone in the other place, so please move it accordingly. Yesterday, I spent ~ 2 hours trying to make the following formula work in MS Excel. I am trying to create an overview page that brings together a list of names from different sheets into a single column. Ideally, there would be no spaces between the names (as I'll be using this function to pull other information from the columns). Eventually, I'll sort the table by the person's name. In order to accomplish this, I decided that some form of an "if" statement would be the best approach (since I'll have to be using different data from ~ 5 sheets). The formula is as follows:

    =IF(AND('Sheet1'!A$2:A$10000="0",'Sheet2'!A$2:A$10000),'Sheet2'!A$2:A$10000,"not true"))

    I've also tried to use the following, but with no success:

    =IF(AND('Sheet1'!A2:A$10000 = 0,'Sheet2'!A2:A$10000=0,'Sheet3'!A2:A$10000=0,'Sheet4'!A2:A$10000=0),"done","not done")
    ^^ This one was used for testing but broke after the first list

    I also tried to use a VLOOKUP function in a nested conditional statement, but with no real success.

    I had some moderate success using this one:
    =IF('Sheet1'!A2:A1007<>0,'Sheet1'!A2:A1007,IF('Sheet2'!A2:A1007<>0,'Sheet2'!A2:A1007,IF('Sheet3'!A2:A1007,'Sheet3'!A2:A1007,IF('Sheet4'!A2:A1007,'Sheet4'!A2:A1007))))

    The problem that I have been running into is that when I switch sheets, the A(X) does not start from the beginning of the column on the new sheet, but rather starts on the next row on the new sheet. For example, if I only had 12 names (row 13 after the headers), then my function would start on row 14 of the next sheet. Have any ideas on how to make this work?
     
    • Like Like x 1

Share This Page