Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 6
There are 1 users currently browsing forums.
|
 |
07-01-2009, 02:34 PM
|
|
Registered User
|
|
Join Date: 01 Jul 2009
Location: US
MS Office Version:Excel 2003
Posts: 7
|
|
|
Multiple If Statements?
Please Register to Remove these Ads
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
|
07-01-2009, 02:38 PM
|
 |
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,517
|
|
|
Re: Multiple If Statements?
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.
__________________
If you've been given good help, use the  icon in that post to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Last edited by JBeaucaire; 07-01-2009 at 02:41 PM.
|
07-01-2009, 04:26 PM
|
|
Registered User
|
|
Join Date: 01 Jul 2009
Location: US
MS Office Version:Excel 2003
Posts: 7
|
|
|
Re: Multiple If Statements?
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
|
07-01-2009, 05:30 PM
|
|
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,517
|
|
|
Re: Multiple If Statements?
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.
__________________
If you've been given good help, use the icon in that post to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
07-02-2009, 08:08 AM
|
|
Registered User
|
|
Join Date: 01 Jul 2009
Location: US
MS Office Version:Excel 2003
Posts: 7
|
|
|
Re: Multiple If Statements?
Any thoughts on what that formula should be?
|
07-02-2009, 09:03 AM
|
|
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,517
|
|
|
Re: Multiple If Statements?
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.
__________________
If you've been given good help, use the icon in that post to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
