Hi,
I am working on a calc file that actually is a pricelist file.
Each line is one product with sku, description, shop category, retail price, wholesale price etc etc.
I want to have at first line one cell to use as search box that will searching 1st column (sku code) and after search will copy the result line to 2nd line.
There are more than 4000 products in file.
I have no idea if this is possible and where to start thinkng about it.
At first should i use search, find or else function?
Thank you in advance for any help
[Solved] Search column from specific cell & copy the result
[Solved] Search column from specific cell & copy the result
Last edited by manosdet on Thu Apr 08, 2021 7:30 am, edited 1 time in total.
Windows 7/ OpenOffice 4.1.7
Re: Search column from specific cell and copy the result lin
This can be done with a set of VLOOKUP functions, one for each column. The first parameter in VLOOKUP would be the address of the "search cell" containing the SKU to be found. The second parameter would be the range of cells containing the data. The third parameter would be the column from which to return data. To get data from the second column, enter 2 as the third parameter. The last parameter should be zero. For example
might be the formula in the cell B2 if A1 holds the SKU you want to search for.
Code: Select all
=VLOOKUP($A$1; $A$3:$F$4000; 2;0)
might be the formula in the cell B2 if A1 holds the SKU you want to search for.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Search column from specific cell and copy the result lin
First of all i want to thank you for your reply.FJCC wrote:This can be done with a set of VLOOKUP functions, one for each column. The first parameter in VLOOKUP would be the address of the "search cell" containing the SKU to be found. The second parameter would be the range of cells containing the data. The third parameter would be the column from which to return data. To get data from the second column, enter 2 as the third parameter. The last parameter should be zero. For exampleCode: Select all
=VLOOKUP($A$1; $A$3:$F$4000; 2;0)
might be the formula in the cell B2 if A1 holds the SKU you want to search for.
Ok, let's say that we have that calc file that is similar to mine but with less products.
Can you give me an example on real how to use the set of VLOOKUP functions?
Windows 7/ OpenOffice 4.1.7
Re: Search column from specific cell and copy the result lin
Notice the formula changes in each column of row 2.
- Attachments
-
- frouta_fjcc.ods
- (17.68 KiB) Downloaded 154 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Search column from specific cell and copy the result lin
wow Thank you so much FJCC !!!
Is it possible to search the SKU by 4 digit?
ie instead of searching for CA29-3226AS the search term will be 3226.
All the 4 digits on 1st column are unique
I found a solution,
I added one more column with 4 digit codes and use it for search.
Thank you again FJCC
Is it possible to search the SKU by 4 digit?
ie instead of searching for CA29-3226AS the search term will be 3226.
All the 4 digits on 1st column are unique
I found a solution,
I added one more column with 4 digit codes and use it for search.
Thank you again FJCC
Windows 7/ OpenOffice 4.1.7
-
- Posts: 1
- Joined: Fri Apr 09, 2021 11:42 am
Re: [Solved]Search column from specific cell and copy the re
I have a basic tutorial for this one. please see link https://youtu.be/_RpKxdtOQ6s
thank you
thank you
Last edited by thomasjk on Fri Apr 09, 2021 8:25 pm, edited 1 time in total.
Reason: Disable live link
Reason: Disable live link
openoffice v4.1.7 windows 10 home