Ask Your Question

how to populate a cell on one sheet from based on criteria on another sheet [closed]

asked 2014-10-09 18:17:11 +0100

SamuelF gravatar image

updated 2015-08-27 12:29:54 +0100

Alex Kemp gravatar image

I need to set up a sheet that takes the value of a cell on sheet 1 and copies it to sheet 2 based on criteria on sheet 1. ex. if cell a2 hold value "x" and cell a5 holds value "n" then copy contents of cell a3 onto sheet 2 cell a3.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 20:10:47.803901


and what if cell A can hold one of two values and I need it to accept either of the two ex. if cell a2 holds value "x" or value "b" on sheet 1 then value of a3 on sheet 2 is equal to the value of a3 on sheet 1?

SamuelF gravatar imageSamuelF ( 2014-10-09 20:24:23 +0100 )edit

Never mind I figured that out but all it gives me is "TRUE" I need it give me the cell value

SamuelF gravatar imageSamuelF ( 2014-10-09 20:24:35 +0100 )edit

Ok, the last thing I need on this is if there is a gap in cells that meet this criteria I need the formula to find the next cell that meets the criteria.

SamuelF gravatar imageSamuelF ( 2014-10-10 15:19:13 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2014-10-09 18:57:52 +0100

Lupp gravatar image

updated 2016-08-04 10:48:15 +0100

Your request is missing the "otherwise". Nonetheless I try to understand this as asking for a formula to place in Sheet2.A3.

Try =IF(And(Sheet1.A2="x";Sheet1.A5="n");Sheet1.A3;"") .

Spreadsheets normally do not copy anything elsewhere, whether conditionally or otherwise. A cell may contain a formula and the evaluator will poll the contents (values) of the referenced cells or ranges and evaluate a result from them. This result will then show up in the cell containing the formula. Copying from a source to a destination can be done a) interactively: 'Copy'/'Cut' and 'Paste'/'Paste Special' ... b) by calling a piece of a real program stored somewhere in the background. (Don't rely on this if avoidable. Otherwise study programming.)

(Editing:) My formula will surely work. Maybe the semicolon as parameter delimiter was used in a locale where the comma is the proper delimiter. For additional remarks and suggestions: See attached file.

Please don't write down the questions to sloppy or abbreviated. Other contributor's time isn't less valuable than yours.

(Editing with respect to the comment by @snurd1:)
The tool to do things @snurd1 thougth to be looked for here, is not offered by Excel, but is a "macro" you may add to your Excel. There are few things, whether sensible or not, you won't find an Excel-VBA macro for. Since it is written as a Sub, you will have the well known problem to pass parameters.

Relying on user code such things can also be achieved in Calc, of course (See my original answer). I wouldn't advise to do so for (again) well known reasons. If urgently needed you may write a Function performing the needed actions by side-effects.

By the way (not to be misunderstood as a recommendation!): The mentioned VBA macro is working also in LibO (tested with V5.3.0.4) if the switch Option VBASupport 1 is used.)

edit flag offensive delete link more


I'm not sure I know what you mean by,"Please don't write down the questions to sloppy or abbreviated. Other contributor's time isn't less valuable than yours". I wrote in simple English and only used the one abbreviation "ex.", which stands for example, which I have seen used on this site before as well as questions that are much more difficult to understand than mine simply because their wording was horrible.

SamuelF gravatar imageSamuelF ( 2014-10-10 15:15:53 +0100 )edit

I don't know what you have available to you, but some spreadsheets do exactly what you want. Unfortunately it is Excel that does this. If that is of some interest to you, Try this URL I was looking here because I wanted to do the same thing in LibreOffice Calc.

snurd1 gravatar imagesnurd1 ( 2016-08-04 02:04:29 +0100 )edit

Question Tools



Asked: 2014-10-09 18:17:11 +0100

Seen: 19,328 times

Last updated: Aug 04 '16