# Automatically filling in cells based on information from another sheet

#### Mixer2

##### New Member
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
ABCDEFG
1
2
3
4
5
6
7
8
9
10
11
12
13Name22Descr2
14
15
Main

Excel Workbook
ABCD
1ProdIDProdNameProdDesc
21Name1Descr1
32Name2Descr2
43Name3Descr3
54Name4Descr4
6
7
8
9
Product

AP

Last edited:
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!

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

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>

Last edited:
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.

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.

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

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

Replies
3
Views
294
Replies
1
Views
298
Replies
2
Views
335
Replies
1
Views
118
Replies
1
Views
150

1,196,484
Messages
6,015,466
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back