Ask Your Question
0

difficult function

asked 2015-03-11 10:54:24 +0200

bakroi gravatar image

Hi,

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 :)

now,

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?

THANK YOU!!!!

edit retag flag offensive close merge delete

Comments

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)

JohnSUN gravatar imageJohnSUN ( 2015-03-11 12:28:24 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2015-03-11 18:55:03 +0200

Lupp gravatar image

updated 2015-03-15 01:43:26 +0200

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

edit flag offensive delete link more

Comments

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

bakroi gravatar imagebakroi ( 2015-03-11 20:51:19 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-11 10:54:24 +0200

Seen: 87 times

Last updated: Mar 15 '15