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
That works perfectly! Thanks for your help!
Me again -Sorry! It worked perfectly with 4 column. Now that I have made the sheet to look more like how its going to end up I can't get the formulas to work? In fact some info have been put into the Cells when they shouldn't. I have been trying for the last 3 hours to work out what I have done wrong but must be missing something stupid? Can you have a quick look at the attached and let me know how I have messed it up if possible. Page 4 is sheet in question and the data is on the 'Risk Info' sheet.
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?