Apache OpenOffice Community Forum

--SOLVED-- Thank you everyone for sharing your knowledge!

Wondering if I can even do this.

I am running OpenOffice 3.4.1 on windows xp.

I have a small business with stock from three supply companies. I am using Vlookup to search for products by code. I am also using a drop down list to select between these companies and their individual prices. I would like to add a forth option to select the best price from the supply house data. I have found a way to do this, but it does not tell me which company the best price is found in.

Is there a way I can add color to the cells, background or font, in the data sheet that will carry over to the worksheet with Vlookup, or add conditional formatting to select color depending on with column the data is retrieved from? What I am wanting is to select 'Best' from my drop down and to have the vlookup choose the lowest priced option and color code it so I can see which supplier it is.

Here is the formula I use for the Vlookup: =VLOOKUP(A51;DataBase.$A$1:$G$1003; IF($C$50=SupplyHouses.$A$1; 3; IF($C$50=SupplyHouses.$A$2; 4; IF($C$50=SupplyHouses.$A$3;5;7))); 0) Currently I have the Min() function in column G of the 'database' which will give me the lower price but with no acknowledgment to the supplier. columns C,D & E are colored to but this does not carry over to the worksheet.

Thank you for any help.

VLOOKUP() will not keep the format of retrieved data. It is "content only". Most likely it is possible to do what you need by way of conditional formatting, but it is difficult to provide a specific suggestion without having your file (or at least a stripped down version of it). Could you attach what you have to a post here?

If you file is too large, or contains confidential info, you could create a "public copy" with only a few lines of dummy data in your database. Just make sure that the general structure of your document is retained.

Hi, and welcome to the forum.

DanielJUrban wrote:Is there a way I can add color to the cells, background or font, in the data sheet that will carry over to the worksheet with Vlookup, or add conditional formatting to select color depending on with column the data is retrieved from?

All functions, including VLOOKUP, return cell values and never cell formatting. You could probably perform the coloring with conditional formatting, but I think it will be easier to use the STYLE function. Create three styles (I called mine 1, 2, and 3) to correspond to your three supply houses. Use MATCH to determine which supply house matches the minimum price, then use INDEX and STYLE in your formula as shown in the attachment.

You can create styles with the Style Catalog dialog (⌘F11 on a Mac, Ctrl+F11 on other platforms).

In the Calc sheet, I use Data Validity and a dropdown in column C. This allows you to choose one of the three suppliers. To choose none of them and use the minimum price, select the cell and press the Delete key instead of using the dropdown.

I would have preferred to develop an answer using your data but you did not 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). My Calc sheet is protected (Tools → Protect Document → Sheet). You can only enter data in the grey cells. Disable the protection by removing the checkmark by Sheet. There is no password.

Read about these functions and Calc features in the Help, in this forum, or in the Wiki.

DanielJUrban wrote:IF($C$50=SupplyHouses.$A$1; 3; IF($C$50=SupplyHouses.$A$2; 4; IF($C$50=SupplyHouses.$A$3;5;7)))

Note that I have used the MATCH function instead of your nested IF functions to determine which column to access. This is similar to Q20/A20 in the VLOOKUP tutorial below.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] VLOOKUP questions and answers

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

MrProgrammer,

I tried twice to edit/attach the file so you can see what I am after and I must be too stupid because its still not in the post. I tried adding it to this reply too.

Even though its not exactly what I am after I think I can manipulate what you have to make mine work. I will try this tonight and if I can make it work I will put solved and close this question.

Thank you for helping me.

Yes, file was too large. Here we go.

Thank you for the help so far, but my issue is not resolved. I uploaded a file that shows what I am after but have not received a response since. If someone could please look over my file and walk me through the steps of how to use match and index correctly I would surely appreciate it. Thank you.

This I can work with. Thank you all that helped. Even the other help with the match and index functions will help with other spreadsheets I will work on later.