I am setting up a workbook in Excel 2010 where information will be put into three different worksheets by different people. Each sheet is set up to populate a cell two columns over with a unique three character identifier (over 22000 different possibilities between the three sheets) if the cell in column A has information in it.
I am trying to condense the information from those three sheets into one summary sheet. Since each sheet has over 7000 possible rows, I would like to pull out any blank cells from the original sheets when it is being copied over to the summary sheet. So far I have the following formula:
=IFERROR(INDEX('Crew 1 Plan '!A8:A7349,SMALL(IF(ISBLANK('Crew 1 Plan '!A8:A7348),"",ROW('Crew 1 Plan '!A8:A7348)-MIN(ROW('Crew 1 Plan '!A8:A7348))+1),ROW(A1:A22032))),"")
This works to bring the information over how I want it from the first sheet (Crew 1 Plan), but I haven’t been able to work out the nested formula to bring in the information from the other two sheets (i.e. Crew 2 Plan and Crew 3 Plan).
Thank you in advance for any help.
Here is a link to the workbook on OneDrive:
https://onedrive.live.com/redir?resid=91FF733170F37244!544&authkey=!ANWGyOAsjlv7Fak&ithint=file%2cxlsx
I am trying to condense the information from those three sheets into one summary sheet. Since each sheet has over 7000 possible rows, I would like to pull out any blank cells from the original sheets when it is being copied over to the summary sheet. So far I have the following formula:
=IFERROR(INDEX('Crew 1 Plan '!A8:A7349,SMALL(IF(ISBLANK('Crew 1 Plan '!A8:A7348),"",ROW('Crew 1 Plan '!A8:A7348)-MIN(ROW('Crew 1 Plan '!A8:A7348))+1),ROW(A1:A22032))),"")
This works to bring the information over how I want it from the first sheet (Crew 1 Plan), but I haven’t been able to work out the nested formula to bring in the information from the other two sheets (i.e. Crew 2 Plan and Crew 3 Plan).
Thank you in advance for any help.
Here is a link to the workbook on OneDrive:
https://onedrive.live.com/redir?resid=91FF733170F37244!544&authkey=!ANWGyOAsjlv7Fak&ithint=file%2cxlsx