Difficult function


I’m trying to find a function that:
collects data (a calculation) and a word (that’s allready written in a cell) on one page, and puts those two togheter in one cell on another page.
for example:

on page 1 I have in cell A1: 5 in B3: 2 and in A3: thisword

on page 2 I like to get in one cell: A1 * B3 and the word from A3

If someone can help with that, I’m allready very happy :slight_smile:


to make it even more complicated, I actually have a long list like that. A1 always stays the same but B4 is different, B5 also, … and the words are different to.
So, on page 2 I think I can figure out how to put them all there(if I get an answer on the first part of the question). But Here is the tricky part (I’m not even sure it is possible), I would like the cell NOT to appear when the value of A1 * Bx doesn’t exists or is zero (sometimes there is nothing in Bx). So actually it filters the words who have no value and doesn’t show them (even not an empty cell)… Is this possible?


even not an empty cell?.. No, it’s impossible with formulas - it’s possible with macro only. If you are willing to accept the empty cells, you can solve the problem with the formula kind of =IF(Sheet1.B3="";"";""&($Sheet1.$A$1*Sheet1.B3)&" "&Sheet1.A3)

It might possibly be done using an extremely complicated formula. This will be very inexpedient and inefficient.

How to (easily) do it with a helper column is demonstrated (for academic purposes only?) in the attached example.

A cell prepared for a result will, of course, contain a formula, and thus not be empty. It will, if so intended, show like being empty, but contain the empty string. ask47508CalculatingAndCompactingResultsUsingOneHelper001.ods

As I dreamt of an efficiency issue with the solution suggested in the above linked in document, I went back to the topic an will now attach a much more efficient variant. The new file is containing both solutions. ask47508CalculatingAndCompactingResultsUsingOneHelper002.ods

This is exactly what I wanted to obtain! Thank you very much!
it is indeed a very complicated formula :slight_smile: