[Dropped] Look up data based on cell value, then multiply

Discuss the spreadsheet application

[Dropped] Look up data based on cell value, then multiply

Postby Flikks » Thu Apr 29, 2021 6:52 pm

Hello,i have a problem im too stupid to solve myself, i want to make a sheet that Takes a price out of column P when i type in the product name from column O and multiplicate it with a number in the same row from Column D.

Any help would be appreciated thank you in advance
Last edited by MrProgrammer on Mon May 10, 2021 9:11 pm, edited 1 time in total.
Reason: OP did not supply an attachment, but asked no questions in over a week so presumed solved
OpenOffice 4.1 on Windows 10
Flikks
 
Posts: 2
Joined: Thu Apr 29, 2021 5:42 pm

Re: Questions about the applying of data validation

Postby MrProgrammer » Thu Apr 29, 2021 7:18 pm

Hi, and welcome to the forum.

Flikks wrote:Takes a price out of column P when i type in the product name from column O
[Tutorial] VLOOKUP questions and answers

Flikks wrote:multiplicate it with a number in the same row from Column D
Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.
Perhaps read [Tutorial] How do I specify the formula for a column?.

To receive any additional assistance from me you must attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 4136
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Questions about the applying of data validation

Postby Flikks » Thu Apr 29, 2021 8:12 pm

thank you for the tutorials, however i still dont fully understand how to setup the formula.
I gonna reiterate my problem again:
i have a list of product names in one column and the corresponding pricelist in the next column, and i want to have a a setup where the correct price will show in column three when i type in the name of the product in column 1 and the quantity in column 2.

i tried to follow the tutorial, this is my (not working) formula =VLOOKUP(C21;O15:P28;3;0)
C21 is the row where i type in the name of the product, O15-P28 is the search table (o are the names and P are the prices)

Thank you for your help :).
OpenOffice 4.1 on Windows 10
Flikks
 
Posts: 2
Joined: Thu Apr 29, 2021 5:42 pm

Re: Questions about the applying of data validation

Postby Villeroy » Thu Apr 29, 2021 9:11 pm

=VLOOKUP(C21;O15:P28;3;0) applied to cell D21 looks up the left neighbour C21 in the range 11 columns to the right (D to O) and 5 rows higher (21 to 15) until 12 columns to the right (D to P) and 7 rows down (21 to 28).
When you copy down this formula one cell down to D22
=VLOOKUP(C22;O16:P29;3;0) still looks up the left neighbour in the range 11 columns to the right and 5 rows higher until 12 columns to the right and 7 rows down.

To make things worse, O15:P28 includes 2 columns and you try to lookup column #3 of 2 columns which raises an error anyway.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30281
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Questions about the applying of data validation

Postby A.Fish » Fri Apr 30, 2021 5:40 pm

Flikks wrote: i tried to follow the tutorial, this is my (not working) formula =VLOOKUP(C21;O15:P28;3;0)
C21 is the row where i type in the name of the product, O15-P28 is the search table (o are the names and P are the prices)


The formula you have (minus the 3rd column error) will only return the dollar amount you have in column P for that product, you will then need to multiply that by the number of that product (your column B) to get the result you are looking for to show in column C.

One thing Villeroy is hinting at is to not forget and use your dollar signs ' $ ' to hold your search table to an absolute range, (ie $O$15:$P$28) otherwise as you drag your formula down the sheet, the range of cells being searched will also change which will cause unexpected results. If you expect the search table to expand/change or you have a lot of products you may also want to consider putting it on a separate sheet.
Windows 7
OpenOffice 4.1.2
A.Fish
 
Posts: 7
Joined: Wed Aug 21, 2019 7:37 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests