Forum Discussion
If column 1 in the top table = 'yes' I want to copy column 2 & 3 into the table below columns 1 & 2.
HiMark_Bry
Perhaps this works better. I have inserted another sheet with the report table and a choice cell.
The formula is using the index function and small function to extract the information based on the choice made.
Hope this works better. Do let me know if you need further clarification.
Cheers
Hi Mark_Bry
The issue you were having was because of the location of the table header. In the earlier formula the header was in row 1, hence we used this formula
=IFERROR(INDEX(T_Data[Column2], SMALL(IF(Report!$B$3=T_Data[Column1], ROW(T_Data[Column1])-ROW($A$1)), ROW(1:1))),"" )
However, the header is now in row 6, hence, we need to adjust the formula accordingly.
=IFERROR(INDEX(T_Data[Column2], SMALL(IF('Risk Info'!$K$7=T_Data[Column1], ROW(T_Data[Column1])-ROW($A$6)), ROW(1:1))),"" )
See attached the updated worksheet.
Cheers.
Hi There. Quick question. I have been trying to understand the formula you sent previously. I get the first part being the array etc. I still cant get it to work on another table?
I have added the following to a table attached:
=IFERROR(INDEX(Table13[[#All],[Tool List]], SMALL(IF(Sheet2!C44=Table13[[#All],[Require/Omit]],Table13[[#All],[Require/Omit]])-ROW($A$43), ROW(1:1))),"" )
I sure it the red bit that I have wrong but the more I try the more confused I get. Can you advise where/why I have gone wrong please?
Mark_Bry There were actually a few problems:
original:
=IFERROR(INDEX(Table13[[#All],[Tool List]], SMALL(IF(Sheet2!C44=Table13[[#All],[Require/Omit]],Table13[[#All],[Require/Omit]])-ROW($A$43), ROW(1:1))),"" )
correct:
=IFERROR(INDEX(Table13[Tool List], SMALL(IF(Sheet2!C$44=Table13[Require/Omit],ROW(Table13[Require/Omit])-ROW($A$43)), ROW(1:1))),"" )
I tried to color code the errors/corrections.
In blue are is the [#All] which means to include the headers and totals in addition to the data, which creates an offset problem and just isn't needed
In the yellow you had C44 without any $ meaning it changed as it went down the table. You need either C$44 (holds row 44), or $C$44 (holds both C and 44) or just hard code it with "Yes" as that really isn't likely to change in this context.
In Red I highlighted that your 'return' value is the cell value from the table when in fact you are trying to find that cells location/row and therefore you needed to have "ROW()" around that value.
In Cyan I show the ")" which closed off the 'IF' statement when in fact you needed to subtract the header row from the returned row before you close off the 'IF' statement.
Hope that helps
