Adding Data by Row based on typing in cell contained in Row

[dkurschner]

Hi All! I am BRAND NEW to this board and my Excel skills are quite marginal compared to the Think Tank on this great message board. I have an ongoing project that has many questions I'd like to pose. I will only start with one question and hopefully you guys/gals are willing to help me "learn how to fish" a little bit better.

As seems to come up fairly often in Excel message forums, I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more.

Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc.

Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.

Is this a daunting task or is it do-able for a excel user without any VBA or much macro experience? Your help would be greatly appreciated .

 

[Sandeep Warrier]

Hello..

It'll be helpful if you could provide some sample data.
From what I understand your data would look something like this.

Excel Workbook
	A	B	C	D	E
1	Mfg	Part #	Qty Used	Cost Ea	Vendor
2	ABCD	Part 1	2	100	Vendor 1
3	EFGH	Part 2	4	152	Vendor 2
4	XYZA	Part 3	1	410	Vendor 3
5	DEFG	Part 4	5	584	Vendor 2
Sheet1

Excel Workbook
	A	B	C	D	E
9	Part #	Mfg	Qty Used	Cost Ea	Vendor
10	Part 4	DEFG	5	584	Vendor 2
Sheet1

I think the formulae give should do the job...

 

[dkurschner]

Sandeep - This is a great formula for starters but I'm experiencing a problem. If I have numbers stored as text in the indexed column, the formula doesn't seem to work. Is there a way around this? Thanks for your help so far!

 

[Sandeep Warrier]

I tried changing some of the sample data to numbers in text format and I didnt get any error. What problem do you exactly face?

Do you get a #Value or #N/A or something like that. If yes, could you select the cell containing the formula and go to Tools-> Formula Auditing -> Evaluate Formula and check where exactly the error occurs.

I also noticed something else, you need to have similar formatting in the cells containing the lookup value & the lookup table.

Since in the example given, you are using Part# as your lookup value, the format for Part# in both tables should be the same. So if it is number formatted as text in one table, it should be number formatted as text in the second table too. I think this is why you are facing the error.

Regards,
Sandeep.

 

[dkurschner]

I stand corrected...I get the "#N/A" whenever there is a blank cell involved or if the item I type is has not been entered into the list yet. I've got about 7 columns as a test using your formula which works well so far. But it would be nice to find a way to eliminate the error message on the blank ones.

Continuing down this same path of thought, If we were to take this one step further, is there a way to incorporate and IF/THEN or AND type of formula where if I type in a Part#, the formula could also reference another column's cell in the same row (like CUSTOMER [not shown on your example] for example and return a value that is specific to the CUSTOMER AND the PART#. To clarify, my list may have some columns that has data that changes depending on the Customer Column when using the same Part#.

I'll post a sample of my sheet if it helps tomorrow as I've got to get some Zzz's. I'll keep monitoring this thread. I really appreciate the help.

 

[Sandeep Warrier]

Hello,

To overcome the error part you could use IF(ISERROR( syntax. I have used the same in the formulae below.

For your 2nd question, if possible, add a column at the end which concatenates Part# & Customer.

Excel Workbook
	A	B	C	D	E	F	G
1	Mfg	Part #	Qty Used	Cost Ea	Vendor	Customer	New Col
2	ABCD	Part 1	2	100	Vendor 1	Cust 1	Part 1Cust 1
3	EFGH	Part 2	4	152	Vendor 2	Cust 2	Part 2Cust 2
4	XYZA	Part 1	1	410	Vendor 3	Cust 3	Part 1Cust 3
5	JKLM	Part 4	5	584	Vendor 2	Cust 4	Part 4Cust 4
Sheet1

Excel Workbook
	A	B	C	D	E	F
9	Part #	Customer	Mfg	Qty Used	Cost Ea	Vendor
10	Part 1	Cust 3	XYZA	1	410	Vendor 3
Sheet1

What the IF(ISERROR( does is it executes the formula & if the value returned is an error like #Value or #N/A, it will say "Value not Present". If no error is returned, the formula would give the required values.

Hope this solves your query..

Regards,
Sandeep.