Now I would like to be able to export data into another Excel Sheet in a following way:
Each filled-in questionnaire is going to contain information on various topics, but the answers are mostly Y/N. Now I would like to find a solution for exporting this data into our datasheet, which in brief contains a column with names of the products and then the following columns reperesent various kinds of information we have about the product. The data here is date of receiving the information.
I have been thinking about it and what I found out myself would be a function like this in each cell:
=IF(SEARCH("wanted answer (e.g. Y)";'[Questionnaire]Sheet'!field where the information is);'[Questionnaire]Sheet'!field where the date is;'[Questionnaire]Sheet'!field where the information is)
This should check the questionnaire file and if it finds the "expected answer", it gives back date when the questionnaire was filled in. If not, it gives back the particular cell's content. (Btw it works well with the correct answer where it returns the date, but if the case is the opposite, it gives an error instead of copy-pasting the content of the cell. This can nevertheless still serve the purpose of warning me or my colleagues that there was something wrong with that information for that particular product.)
However, since this is about about a thousand products and about 10-15 variously overlapping categories depending on the product itself, I think to write such a formula into each cell is overwhelming amount of work. Although one can copy+paste, there will still be a manual correction needed every time. I'm also not sure how would Excel handle with so many chains. On top of it, when the questionnaire gets updated and subsequently the position of the information on the sheet changes, all the enormous work will be wasted. Does anyone have an idea what I could try? Thanks a lot!