IF= Formula

**BFelt** · 08-24-2011, 10:50 AM

Im trying to create a spreadsheet. I have 2 sheets, 1 with the unit numbers i have to use the second with issues the product could have. I would like to be able to just enter a number 1-50 and whatever # i input the issue i have on my second spreadsheet would be inputed. for example.

file attached

i will also be entering or need to enter more than one issue per unit #.

Thanks for the quick responses.

**Andrew-R** · 08-24-2011, 10:56 AM

It would be helpful if you posted an example workbook, so that we could see the layout of your data (and also if you changed the title of this thread to something a bit more descriptive), but...

Let's say your first sheet has the unit numbers in column A, starting at row 2, and you're going to input the problem # in column B. Let's also assume that your second sheet is called Issues and has the list of issues in column A, starting at row 2.

On the first sheet, in C2, put the formula =IF(B2<>"",INDEX(Issues!A:A,B2+1),"")

That should work.

**BFelt** · 08-24-2011, 11:29 AM

Originally Posted by Andrew-R

It would be helpful if you posted an example workbook, so that we could see the layout of your data (and also if you changed the title of this thread to something a bit more descriptive), but...

Let's say your first sheet has the unit numbers in column A, starting at row 2, and you're going to input the problem # in column B. Let's also assume that your second sheet is called Issues and has the list of issues in column A, starting at row 2.

On the first sheet, in C2, put the formula =IF(B2<>"",INDEX(Issues!A:A,B2+1),"")

That should work.

Set it up like u've stated above, but am not gettign results i would like any results for that fact

**Andrew-R** · 08-24-2011, 11:36 AM

The formula works fine, but I'm guessing the structure of your workbook, which is probably why it isn't doing anything. Can you post an example workbook for me to look at?

Here is a sample workbook I created, based on what I think you're trying to do and which shows the formula I gave you working.

**BFelt** · 08-24-2011, 11:38 AM

i have attached my workbook to first post. I am needing to more than 1 issue per unit number probably about 10-20 issues per #. on the first sheet i noticed i did not recognize a issues coloum next to the unit #

**Andrew-R** · 08-24-2011, 12:33 PM

OK - what you're after would be very tricky with formula, or fairly easy with macros. Do you have any objection to having some macro code in your workbook?

**BFelt** · 08-24-2011, 12:36 PM

im not sure what u mean by macros but im sure it wouldnt be a problem having it in my spreadsheet

**BFelt** · 08-24-2011, 02:00 PM

how easy of a macro would this be and what would you start with? thanks again for quick responses.

**Andrew-R** · 08-24-2011, 02:09 PM

Excel has it's own in-built programming language, called VBA (Visual Basic for Applications) - it's very flexible and can do many, many things that aren't possible with just worksheet functions, but it's also probably harder for a new Excel user to understand.

I've attached your example book, with a macro added.

Basically what it now does it when you enter a number in column B of the first sheet it will look-up that issue on the 2nd sheet and put the value in column C and then clear cell B. You can add multiple problems by putting the numbers one at a time into column B.

If you put a negative number in column B it will remove that issue from the list in Column C.

Neither column contains any formula, so you can type over them as you see fit and it won't alter the operation of the macro.

However, now when you open the workbook you'll have to confirm that you want the macros enabled (Excel will prompt you).

Thanks.