How do I auto populate qty from a cell adjacent to another cell

**bamma86** · 08-03-2012, 11:22 AM

I have two worksheets in the same workbook. I need to insert a formula in cell I16(sheet 2) that says

If any of the cells between A39:A42 (sheet 1) equal 51, then insert qty from cell C39:C42 (sheet 1), adjacent to the cell that equals 51, into I16 (sheet 2)

ex: which ever cell has the "51" thats the adjacent cell to pull from i.e. A40=51(sheet1) then insert qty from C40(sheet1) into I16(sheet 2)

How do I write a formula for that

**ChemistB** · 08-03-2012, 11:27 AM

There are a number of possibilities.

How about
=IFERROR(VLOOKUP(51,Sheet1!$A$39:$B$42,2,FALSE),"")

**bamma86** · 08-03-2012, 12:23 PM

This formula isnt working. Nothing is appearing in cell I16.

**day92** · 08-03-2012, 12:51 PM

Bamma it might help if you upload some sample data with expected results.

**bamma86** · 08-03-2012, 01:04 PM

what do you mean day92? I dont understand.

**ChemistB** · 08-03-2012, 01:07 PM

"Go Advanced" > Manage Attachments and attach a spreadsheet with some data showing what you have and what you expect to happen. That will help me understand why the formula I gave you is not working. Otherwise, I'm just guessing.

**bamma86** · 08-03-2012, 01:45 PM

THE BLUE CELL ON SHEET 3 IS WHERE I NEED THE FORMULA TO BE.

IT SHOULD SAY "IF ANY CELL BETWEEN A10:A13 (SHEET 2) SAYS 51 THEN MULTIPLY QTY IN C_ (SHEET 2) BY C8 (SHEET 2)."

NOTE: C_ SHOULD BE QUIVALENT HORIZONTALLY TO WHICH EVER CELL HAS "51" IN IT. i.e. A11=51 THEN MULTIPLY C11*C8 OR IF A12=51 THEN MULTIPLY C12*C8

IM NOT SURE HOW TO MAKE THE FORMULA READ BECAUSE OF THE FACT THAT THERE ARE SO MANY VARIABLES.

**bamma86** · 08-03-2012, 01:47 PM

Also sorry for the all caps. I didnt realize until I was half way done.

The info on the spreadsheets that i sent is just a small portion of what I am doing on a MUCH larger scale, but if I can get a formula for it i can just copy and paste.

The highlighted cells are the ones I am focusing on solely for the sake of the question.

**icestationzbra** · 08-03-2012, 01:54 PM

http://www.excelforum.com/excel-gene...auto-fill.html

duplicate thread, or just an error in attaching file?

---------- Post added at 01:54 PM ---------- Previous post was at 01:50 PM ----------

does this work for you in Sheet3!C10?

Please Login or Register  to view this content.

**bamma86** · 08-03-2012, 02:03 PM

I dont think that seems right. Im not trying to multiply anything in column "A". I am only using that as a reference so that sheet 3 always multiplies the qty of LABORERS. 51 is just the code from the legend on sheet 1 that means LABORER

**icestationzbra** · 08-03-2012, 02:06 PM

the following formula:

Please Login or Register  to view this content.

is doing the following deed:

Originally Posted by bamma86

IT SHOULD SAY "IF ANY CELL BETWEEN A10:A13 (SHEET 2) SAYS 51 THEN MULTIPLY QTY IN C_ (SHEET 2) BY C8 (SHEET 2)."

NOTE: C_ SHOULD BE QUIVALENT HORIZONTALLY TO WHICH EVER CELL HAS "51" IN IT. i.e. A11=51 THEN MULTIPLY C11*C8 OR IF A12=51 THEN MULTIPLY C12*C8

i.e., between A10 and A13, any row that has 51 in it, fetch the corresponding value from column C (between C10 and C13) and multiply that value by the value in C8 - all these values are from the Worksheet tab.

instead of looking at the formula, check to see if the result is what you desire. if it is not, i will help fix the formula.

**ChemistB** · 08-03-2012, 02:12 PM

If there will only be one instance of 51, then this should work

Formula:

Please Login or Register  to view this content.

**bamma86** · 08-03-2012, 02:52 PM

HAHAH!! Im glad you were confident in our formula because it sure enough worked lol. Thx.!!

---------- Post added at 02:52 PM ---------- Previous post was at 02:51 PM ----------

Thx icestationzbra

**icestationzbra** · 08-03-2012, 03:14 PM

actually, when i look back at your spreadsheet, you have already used a formula in there that you could have used for this purpose. you have utilized INDEX/MATCH functions in the workbook, and they could be extended thus to achieve your desired results in Sheet3!C10:

Please Login or Register  to view this content.

**bamma86** · 08-03-2012, 04:25 PM

I thought I would be able to use that formula, but I couldnt figure our how to "word" it for lack of correct terminology. Im not really that talented with Excel. Although my boss seems to believe that I am, I just know how to navigate my way through.

How do I auto populate qty from a cell adjacent to another cell

LinkBack

Thread Tools

Rate This Thread

Display

How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Re: How do I auto populate qty from a cell adjacent to another cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions