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
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?
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
Thanks again! I thought that part was to remove the blank rows - I get it now! Although I do have 2 more questions (Sorry). I have attached the updated version again. Is there any way to keep the row height auto fill once you un select from the info sheet? When I take out a row of info I want it to go back to a standard height automatically. Is this possible? Also Im trying to populate most of the info on the whole workbook by checking the info on the site row (Info sheet) and then select the site on page 1. It is all working as I want apart from the site contacts on page 3. If no entry then it shows 0. Ive added an IFERROR but cant get rid of it? If you would be so kind to try and answer the questions it will be really appreciated! I cant tell you how much I have learned and benefited from your answers/solutions. wumolad
Hi Mark_Bry
I didn't quite get what you mean by this:
"Is there any way to keep the row height auto fill once you un select from the info sheet? When I take out a row of info I want it to go back to a standard height automatically. Is this possible?"
Maybe you can paraphrase.
For the cells showing 0 when they are blank in the other table, you can use custom formatting [0;-0;;@]. I have already applied this to page 3 and it works fine as those with zero value are now blank.
See attached the updated workbook.
Cheers,
