Auto Input

**jcolvin86** · 08-19-2010, 10:36 AM

Hello,
I was wondering if it is possible to auto input data from "sheet2" to "sheet1" when I type in the number of the "wanted" row.

For example: I have a sheet filled with formulas for bidding work. I enter data into each separate cell right now and the formulas calculate the "sum." I was wondering if it is possible to use "sheet2" as a database and fill in all those cells and formulas, then when I want to select a particular "product/cell" all I have to type into "sheet1" is the corresponding row, or even better make it be related to "column C."

Better Example: SHEET2= database
Sheet2 ~ material = BL8, cost = 1.15, multiplier = 1.125
~~~~~~~material, cost, multiplier = separate columns.
Then what I want to do is in "sheet1" be able to type:
sheet1 ~ type in ~ material = BL8,
~ then have the cost column and the multiplier column auto fill.

I dont know if I can use a separate formula or if I need a MACRO. I do not have the knowledge to come up with my own MACRO.

Right now I am taking up columns A-R.

Sorry if this is already answered in another thread. Could not find it myself.
Thank you in advance for any help.

**StanPO** · 08-19-2010, 11:33 AM

Possible solution is attached. I hard coded your values into Sheet2. Look at the VLOOKUP functions on Sheet1 in Columns "G" and "P".

**jcolvin86** · 08-19-2010, 11:49 AM

That is exactly what I am looking for. Thank you very much. Now how can I do two more things.
1. Include column H which is the dividing factor
2. right now it is set for 119 rows, can I make it more if need be at any point?

I see 119 in the formula bar...can i change it to whatever I want? Also what does the 5, 0 mean after the 119.

Again thank you very much. That is perfect. Now all I need is to fill out the database... and include the dividing factor.

**StanPO** · 08-19-2010, 01:50 PM

It might be helpful to read up on how VLOOKUPs work. Here's a good link:
http://www.timeatlas.com/5_minute_ti...ookup_in_excel

To briefly answer your questions:
1) Model the Column "H" VLOOKUP based on the formula in Column "G". You'll need to change two parameters: the range (from Col "G" to Col "H") and the column (from "5" to "6").
2) Yes, you can change this for any number of rows.
3) The 5 means that the VLOOKUP is pulling in the 5th column from your range.

**jcolvin86** · 08-19-2010, 02:12 PM

Ok,
thanks again. I changed the G to H and 5 to 6 and it worked how I wanted it.
Now, I suppose that brings another question.
Is there anyway I can set the formula to support "and, or."
So instead of having two different vlookup formulas, to have one that is "and, or."
If nothing is entered in cell G but is entered into cell H it will be able to determine that with one formula.
Something like:
:$G$or$H$119,5or6,0
I know "or" wont work but that is the concept I want.

**StanPO** · 08-19-2010, 03:14 PM

I'm not sure what your limitations are, but the easiest way around this is to get rid of Column "H" entirely and use factors < 1 in Column "G".

If Column "H" needs to stay, then one way to accomplish this would be an if statement. For brevity/clarity, this is only pseudocode of what it would look like:
=IF(VLOOKUP(Col G) = 1, VLOOKUP(Col H), VLOOKUP(Col G))

**jcolvin86** · 08-19-2010, 03:20 PM

Yeah,
I need to keep column H because it signifies a different value for multiplication purposes. It might not be possible. Just thought it would be nice. You have already gotten me farther than I ever would have and now it is doing what I want it to do. Just did not know if I could add a couple of columns to one VLOOKUP formula. I def. can live with this though. It is a huge help and will make life much simpler after I fill out the database sheet.
Thank You again.

**jcolvin86** · 08-19-2010, 10:45 PM

Does anyone else know if it is possible to add a couple of columns to one VLOOKUP formula?

**jcolvin86** · 08-20-2010, 10:57 AM

Thanks again Stan. You helped a great deal. Reading some of the help articles from Microsoft do nothing for me, but you helped me understand a little better the vlookup funtion. Two thumbs up to you.