D
Dallas101
I am using the formula below to list what companies in an area did not
have membership access.
=IF(D4="0",A4,"")
Cell D4 is the number of members who have/don't have access. Cell A4 is
the company that the member has/doesn't have access to. Column D will
always either have a 0 or will be a string of numbers. This formula
works except the new column that contains the list will have sporadic
blank cells because those companies had membership access.
How do I move data up into the next cell or automatically delete the
cells that don't have a company name in it? I need it to be automatic
so when the data is entered, the report of companies that are listed is
automatically created. That way others who are not familiar with excel
at all can just print the report. I found one formula that would work:
=IF(ROW()-ROW(NoBlanksRange)+1ROWS(Blanksrange)-COUNTBLANK(Blanksrange),"",INDIRECT(ADDRESS(SMALL((IF(Blanksrange"",ROW(Blanksrange),ROW()+ROWS(Blanksrange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(Blanksrange),4)))
Except that since I have a formula in the referencing cell it is not
working. I am pulling the data from the top of the worksheet and
creating a new set of columns at the bottom of the same worksheet. I am
kind of new to formulas and I am not sure how to apply both formulas so
that I get the correct list of companies and have it listed without
blank cells.
Thank you for your help.
have membership access.
=IF(D4="0",A4,"")
Cell D4 is the number of members who have/don't have access. Cell A4 is
the company that the member has/doesn't have access to. Column D will
always either have a 0 or will be a string of numbers. This formula
works except the new column that contains the list will have sporadic
blank cells because those companies had membership access.
How do I move data up into the next cell or automatically delete the
cells that don't have a company name in it? I need it to be automatic
so when the data is entered, the report of companies that are listed is
automatically created. That way others who are not familiar with excel
at all can just print the report. I found one formula that would work:
=IF(ROW()-ROW(NoBlanksRange)+1ROWS(Blanksrange)-COUNTBLANK(Blanksrange),"",INDIRECT(ADDRESS(SMALL((IF(Blanksrange"",ROW(Blanksrange),ROW()+ROWS(Blanksrange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(Blanksrange),4)))
Except that since I have a formula in the referencing cell it is not
working. I am pulling the data from the top of the worksheet and
creating a new set of columns at the bottom of the same worksheet. I am
kind of new to formulas and I am not sure how to apply both formulas so
that I get the correct list of companies and have it listed without
blank cells.
Thank you for your help.