ISBLANK formula that goes off of IFERROR formula

I currently have a very long formula that ultimately is a using IFERROR. I would like to have a formula that says, if the outcome of the IFERROR is blank, use this other very long formula that also is an IFERROR formula. I then want the formula to say, if this second IFERROR formula results in a blank, use a third IFERROR formula and so forth around 7-8 times. Is this doable, is this understandable? I will include the IFERROR formula that I am currently using and one of the other IFERROR formulas that I want to be used if this first IFERROR statement results in a blank.

Formula 1
=IFERROR(INDEX('Sheet1'!$D$11:$D$242,SMALL(IF('Sheet1'!$I$11:$I$241>0,ROW('Sheet1'!$A$11:$A$241)-ROW('Sheet1'!$A$11)+1,""),ROWS('Sheet1'!$A$11:A11))),"")

Formula 2
=IFERROR(INDEX('Sheet2'!$E$11:$E$147,SMALL(IF('Sheet2'!$C$11:$C$147>0,ROW('Sheet2'!$A$11:$A$147)-ROW('Sheet2'!$A$11)+1,""),ROWS('Sheet2'!$A$11:F11))),"")

I was thinking that the combination of these formulas could result in a formula as follows:

=IF(ISBLANK(IFERROR(INDEX('Sheet1'!$D$11:$D$242,SMALL(IF('Sheet1'!$I$11:$I$241>0,ROW('Sheet1'!$A$11:$A$241)-ROW('Sheet1'!$A$11)+1,""),ROWS('Sheet1'!$A$11:A11))),""),"FALSE",IFERROR(INDEX('Sheet1'!$D$11:$D$242,SMALL(IF('Sheet1'!$I$11:$I$241>0,ROW('Sheet1'!$A$11:$A$241)-ROW('Sheet1'!$A$11)+1,""),ROWS('Sheet1'!$A$11:A11))),""),IF(ISBLANK(IFERROR(INDEX('Sheet2'!$E$11:$E$147,SMALL(IF('Sheet2'!$C$11:$C$147>0,ROW('Sheet2'!$A$11:$A$147)-ROW('Sheet2'!$A$11)+1,""),ROWS('Sheet2'!$A$11:F11))),""),"FALSE","")

Instead of actually putting "" as the result of the iferror, just put the next iferror there.

=IFERROR(INDEX('Sheet1'!$D$11:$D$242,SMALL(IF('Sheet1'!$I$11:$I$241>0,ROW('Sheet1'!$A$11:$A$241)-ROW('Sheet1'!$A$11)+1,""),ROWS('Sheet1'!$A$11:A11))),"")

=IFERROR(INDEX('Sheet1'!$D$11:$D$242,SMALL(IF('Sheet1'!$I$11:$I$241>0,ROW('Sheet1'!$A$11:$A$241)-ROW('Sheet1'!$A$11)+1,""),ROWS('Sheet1'!$A$11:A11))),nextiferrorgoeshere)

Would I then continue to place more IFERROR statements in the "" at the end of each IFERROR?

Yes, in theory.
But that's going to get very long winded and you may end up exceeding the maximum length that a formula can be.

There may be a much easier way, but it's difficult to tell just by looking at a formula.
This was just a straight up answer to the specific question posted.

Can you attach a sample workbook, and describe in words what the formula is actually meant to do?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I would be more inclined to keep the 7-8 iferror functions in their own separate cells.
Then use another formula to test those 7-8 cells for the result you're looking for.

Desensitizing the info would require me creating an entire new workbook to test what I am working on, so that will take some time. The reason I am trying to combine the formulas into 1 formula is so that I can conserve on cell space as I am required to be within an area of space. I can explain what I am trying to do though in words at the least.

I am trying to create a formula that will copy data from a different sheet if a value is true. If a value is true, it will grab the value and then move onto the next value that is true and copy the data so forth until all true data is copied over, then it will create blanks for as many cells as I have the formula copied into. I have a formula like this for 7 or so different sheets in my workbook. I am trying to combine the formulas for each sheet into a single formula. I will attempt to recreate what I am trying to do shortly.

Sometimes it's easier to just manually manipulate data by hand.
That's just going to be a monstrous formula.

I would 'by hand' combine the data from all 4 sheets into 1 sheet, then delete the blanks from the quantity column.
Done. Just a minute or two's worth of work.


I could make a macro that would do it, are you ok with a VBA solution?

The actual workbook I am using has hundreds of lines worth of data that I would need to 'by hand' go through in order to create a single sheet with deleted blanks. Also, the sheets would still need to exist with all of their information in its entirety as anybody using the sheet that would like to add or remove ordered items would want to see the other items that can be ordered. I'm not sure I fully follow what you are stating is an option?

Sorry, I don't see any way to do all of that with 1 formula.

It's Hard enough to look through multiple sheets.
But each sheet isn't even structured the same (according to original formulas in your first post)
First formula is indexing column D, testing column I for >0
Second formula is indexing column E, testing column C for >0

And nesting the iferrors as I originally suggested won't work either.
I didn't really even look at what the formula was actually doing at the time, I was just going by Instead of "", do another iferror.
But that won't really work here, because if the first iferror finds a value for the first row, then the first iferror in the 2nd row does not find a match, then the 2nd iferror will be returning the 2nd value from the 2nd sheet.


I can only envision as I said before, manually consolidating all the data into 1 sheet.
But that proves even more difficult, again because each sheet is not structured the same way.
So you'd end up pasting Part #s from the 2nd sheet into the column where you put the Price from the 1st sheet.


Or, stick with the way you have it. 1 table for each sheet on your Master Tab.

Yeah I figured the IFERROR of the 2nd formula would return the 2nd IFERROR value and I am currently attempting to fix that but not having much luck yet. The structure of every single sheet is the same EXCEPT for the very first sheet which is structured differently and I cannot actually change the structure of that sheet. 1 table for each sheet in my Master Tab is the simplest working option, but not the best looking so ill keep poking around different formulas to determine if I would be capable of doing this.

Thanks.