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.
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?
Never mind I figured that out but all it gives me is “TRUE” I need it give me the cell value
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.
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.)
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.
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 http://www.techonthenet.com/excel/macros/search_for_string.php. I was looking here because I wanted to do the same thing in LibreOffice Calc.