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.
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
Yes - While at home I thought I would put together a set of RAMS which can be used/changed for all sites by just selecting info. As you will know it takes a while to do normally. The RA is similar to one I seen online which I liked, so changed the layout/content slightly to suit what I'm trying to achieve. Just trying to get the data input part sorted then I can add a lot of info plus the standard bits. To be honest with my basic knowledge of Excel I have been amazed how little I know and how much you can actually do. Thanks to both of you for your help in getting me to this point. I'm hoping I will be able to do the rest myself (the easy part). I really would not of been able to work it out without your guidance and support. Very much appreciated!
wumolad What I believe Mark is asking about is if you change row 9 (cabling under floor) from yes to no the table on page 4 leaves the row that previously had that row information at a very tall row height even though the new row doesn't require it. Conversely if you autoheight the rows without "row 9" selected and they are all short/normal height then when you add "row 9" the wrapped info is cut off.
@Mark_Bry First off, being a quality & regulatory person I loved seeing this risk assessment. lol. And by RAMS is that from Emergo? Regardless, I'm wondering why you don't use a pivot table for this? I have mocked one in the attached workbook. after you make a change you do need to 'refresh' (alt-F5) but it seems to work pretty well. Formatting still needed to make it look pretty...
Also you should know that 2 limitations of the solution you are using include:
a) if you copy that formula down to row 30 for example but you have more "yes" items than the number of rows you copied that formula into then the list will be truncated (i.e. some "yes" rows not shown). Of course you can just copy that into 100 or a few hundred rows to be sure but that isn't great and you'll need to deal with page formatting as excel will think all those rows have values. Another option is to do something realistic (let's say 50 even though I know risk assessments can be very large) and then create a "warning" (I usually do it somewhere near the top left) that checks if that last cell has data in it to check if additional rows of formula are needed.
b) all those array formulas can slow down excel. Now in my case I switch from a formula like that to pivot tables in a sheet where we had thousands of lines and although some of my risk assessments have hundreds of lines I doubt any of them reached thousands so this may not be too much of a concern.
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,
