Automatically filling in cells based on information from another sheet

[Mixer2]

Hi I am new to these forums and havent worked on excel in some time now. At work I am now using excel for most of my projects. I want to make things easier for myself when filling in my worksheets by having certain cells autofill based on a product number.

So I have my main worksheet and I have my product information sheet. When I type in the product number in cell 13C, I want it to auto fill cell 13B with the product name which is located in collumn A on the product info sheet, but dependent upon that product number. Collumn B in my product info sheet has all of the product numbers. From there I would need 13G on my main worksheet to fill in with Collumn D from the product sheet, but again dependent upon that product number.

There are more that need to fill yet, but I can figure out what to do once I have a starting point with the above I believe.

Thanks for any help!

 

[ArthriticPanda]

Hi Mixer2,

A couple of =VLOOKUP formulas should help:

I realise I've used column C instead of D in the products but I needed a table with no gaps. Presumeably you have something else in column C...

Excel Workbook
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9
10
11
12
13		Name2	2				Descr2
14
15
Main

Excel Workbook
	A	B	C	D
1	ProdID	ProdName	ProdDesc
2	1	Name1	Descr1
3	2	Name2	Descr2
4	3	Name3	Descr3
5	4	Name4	Descr4
6
7
8
9
Product

AP

 

[Mixer2]

This is really close. The only thing is that the product may not be the same for row 13 every time, so it needs to be able to look up the product number then fill in the information that corresponds with the same row as the product number on the product sheet. Does that make sense?

So if I type in 4 in C13 it will fill in name4 in B13 and descrip4 in G13. In other words, row 13 isnt always going to be the same product, it needs to be able to look up the product according to its number and fill in the appropriate information in the other cells.

Again, thanks for the help!

 

[ArthriticPanda]

Hi Mixer2,

Yes, that vlookup formula will do that for you. Whatever ID number you type in, it will lookup the relevant product data and return it to whatever cell you identify.

I'll attach my example file and you can play about with it. Enter any ID 1-4 and it will pull the corresponding data and you will see what I mean.

https://www.dropbox.com/s/y56x3qx51...s based on information from another sheet.xls

AP

 

[Mixer2]

ADVERTISEMENT

OK messing around with this a bit it brings up a question. Will this not work if there is a blank cell in the product sheet somewhere? Say if you had a product number 1, 2, no number for 3 yet but one for 4? Some of our products here are without numbers barring testing, on those I will just have to enter my information manually.

Sorry about my limits in explaining these. I am unable to download anything or upload anything being at work. I am blocked. I cant even open your example sheet to mess with.


Also could you explain where each part of the equation is referring to? Thanks.

B13	=VLOOKUP(C13,Product!A2:C5,2)

<TBODY>
</TBODY>

 

[ArthriticPanda]

Hi Mixer,

Yes it should still work if there was a blank column at C and Descr in D as long as the range was correct and the lookup column ref was correct.

To explain;

The formula would be typed into cell B13. This will return the result in that cell.

The lookup part will lookup the value typed into cell C13 (in this case a number between 1-4) in the range A2:C5 on the Product sheet.

The number at the end of the formula identifies the column number to look for the result which will match the number typed in, in this case the 'Name2' is in the 2nd column on the same row as the number 2.

Hopefully this will give you an idea of what is happening.

 

[Mixer2]

ADVERTISEMENT

Thanks that is helpful. Is there a limited number for the range? I have the formula working in my cell but it keeps referring to the details on row 91 of the product sheet. When I have: =VLOOKUP(C13,Aggregates!A2:G158,1) it shows A91 in from the product sheet in my cell B13 on the main sheet. When I try: =VLOOKUP(C13,Aggregates!A2:G158,3) it shows C91 on the product sheet in B13 on the main. Aggregates is the name of my product sheet btw.

 

[ArthriticPanda]

Should work. Any chance I can get a look at your sheet? I'll PM you with an email address if that suits.

 

[Mixer2]

Yes PM me your email, I will try to send it to you to look at.