Need Help with formula for PRODUCTS and PRICES ,proberly really basic but im new to this!

[mb00]

Hi i need help with an excel formula im trying to make a basic spreadsheet so i can keep track of products i sell. I want to be able to select a product from the dropdown box and for the price of the product to appear in the price column. I have saved the products and their prices on the next spreadsheet page as a basic database, but i am unable to be able to find a formula i need to make the prices appear ones the product is selected.

Currently i am using the formula: =IFERROR(LOOKUP(J7,productdata),”Select Product”)

but it is only bringing up one price for one product. But if i select any other product that someone has bought it comes back with the error #NAME?


Please help i am new to this

[NeedForExcel]

You will need a simple Vlookup.

Please see the file attached.

See the formula in the selected cell

[mb00]

Now that i have the formula that works: =VLOOKUP(J12,Products!$C$3:$D$500000,2,0) i edited the letters to match my columns. I just need to know how to keep the cell Blank if no product is selected, is there a formula i should include in the previous formula that was given? As if i copy the formula to the rest of the column it will come up with the error #N/A if no product is selected from column J. All i want to do is keep it blank if no product is selected.

Thanks for your help

[Aardigspook]

Hi,

Try this:

Please Login or Register  to view this content.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[mb00]

YESSSSSSS IT WORKED!!!! You guys are awesome... thanks sooo much

[mb00]

OK I Spoke too soon something happened, the formula worked to make the cell blank but then when i selected the product again the price didnt come up anymore?

And, another thing how do i copy the formula all the way down the column, and for it to pick up the cell number of that cell for example:
=IF(ISBLANK(J7),"",VLOOKUP(J7,Products!$C$3:$D$500000,2,0))
and
=IF(ISBLANK(J78),"",VLOOKUP(J8,Products!$C$3:$D$500000,2,0))

and not just copy the same formula of =IF(ISBLANK(J7),"",VLOOKUP(J7,Products!$C$3:$D$500000,2,0))

thanks guys

[JohnTopley]

Please post sample file showing problem and expected results.

[Aardigspook]

Try pressing F9 after selecting a product. If the price then comes up, you've somehow got automatic calculation turned off. To turn it back on, go to the File tab, select Options (at the bottom), scroll down to the Calculation options section and select 'Automatic'.

To copy the formula all the way down, first select J7. The cell will get a box around it to show it's selected, with a little square box at the bottom-right. Left-click and hold this, then drag it down the column as far as you need it to go.

Hope that helps.
(and that I'm not telling you something obvious you already knew!)

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[mb00]

I have attached the sample spreadsheet that im having issues with. Unfortuantly its not the 'F9' issue. So what i need is when i select a product (which are saved in the database on the next page in a table) that the cost of the product appears (i need the formula to work in that whole column) in this spreadsheet the products can be selected in the drop down box in the J column, when a product is selected the price of the product should appear. I also need the cells under 'Cost' in the N colomn to appear blank unless a product has been selected, as if i have copied the formula like i need down the column i am going to need those cells blank until i select a product. Right now if a cell is blank in the J column and i have the formula copied to that cell like in n12 it will show an error of #N/A instead of a blank cell.

I also need it to take the 'Quantity' into consideration. so it will take the price of the product that was selected in the J column and times it by the quantity, and have the price appear in the Cost 'N' column.

Thanks guys

(p.s this probably shows how new i am to this)

[Aardigspook]

I'll have to look at this at home tonight, as I'm on 2003 at work and the file doesn't convert fully. Hopefully someone else can jump in and solve this before then, but if not, I'll get back to you.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[JohnTopley]

See attached ....

[Aardigspook]

File from #11 looks fine to me - does it work for you? If so, glad we (together) could help.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[mb00]

Thanks alot to everyone that helped out. I took the sample that was created by JohnTopley and used that saving me from trying to copy the code.
All you guys a great, very much appreciated!