Im new to this forum. But I have a little bit of excel excperience.
I have a spreadsheet with two pages .on the first page i have an invoice on the second is a product list.
What i would like to do is be able to insert the "product number" on the first page and have it pull the information from page two.
on the product list page i hace three colums: 1- product number, 2- description, 3- price.
So what i would like to ba able to do is on the invoice insert the product number and have excel auto fill the description and price from the second page.
Is this possibe? i know i could do it with multiple IF statements but i have around 300 products.so the IF statement got to be a little long. and i also had to do an if statement for each column. ie description & price.
Any help will help
Very simple to do. take a look at the attached sample, it shows how a simple INDEX/MATCH works. It uses a "named range" called "Materials" for all the values in the A column, but you could just as easily just use a regular range reference.
You can have your list on a separate page, too, and in that case the "named range" makes the formula even easier to read.
Last edited by JBeaucaire; 07-01-2009 at 03:41 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank You, that did exactly what i wanted it to do.
One more thing, in the end of the formula, the 0 that is at the end. That is supposed to return a 0 if the cell does not match anything in the index right?
Well it does not do that, i have multiple lines on the invoice, so i copied the formula all the way down, it works except if i i dont put an item number in the invoce it brings back #N/A in all the fields. And i have the invoce set up to auto sum the totals.
So if i only put one thing in the invoce it brings back that total, then where it adds up all items it gives me the #N/A because the formula.
Any help would be appreciated.
thank you
No, 0 means FALSE, which means "exact matches only". 1 means TRUE, which allows "fuzzy matches" if your data is properly sorted.
To eliminate the #NA errors, you'll need to use another formula up front to see if the code exists, if not, don't bother with the INDEX/MATCH.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Any thoughts on what that formula should be?
In my original sample above, I would test if the MATCH function returns a numerical answer or not:
=IF(ISNUMBER(MATCH($I2,Materials,0)),INDEX(B$2:B$31,MATCH($I2,Materials,0)),"")
If it does, it executes the INDEX/MATCH, if not, it returns "", or "No Match", or whatever you choose at the end.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks