How to create a custom formula?

[jtyson]

Tried searching and didn’t get anything so I apologize if this is already covered. I feel like what I’m looking for is pretty simple. Trying to make our company time sheet more simple. I need to create a formula (or multiple) that basically says Company A = Group A, Company B = Group C, Company C= Group B, etc. So when someone selects Company A from the drop down menu in cell B2, in the corresponding cell B4, Group A automatically populates.

I tried searching youtube and found videos about conditional formatting and creating a formula within the developer section, but frankly I didn’t really understand. Hoping one of you good folks can give me a hand.

Edit

Alright trying to wrap my head around what you said. If I’m just too simple for this, please don’t hesitate to tell me. They can pay someone else to do this if they have to.

I have my lookup table created, but I don’t understand the formula. I’m going to try and explain it very simply and hopefully you can tell me where I’m wrong or what I need to do. I’ve got all the companies listed in column A and their corresponding group in column B. What would the formula be to assign value?

Then tell me if this is correct. On my billing sheet I’d put this formula into a cell in Column D which is where the group name goes for the company listed in Column B in the corresponding row. =VLOOKUP(B2;A1:B192;2;0)

• Admittedly, I don’t understand the formula, just pasting from your comment while changing the value to match my lookup sheet.

Edit #2

I’ve attached a version of my spreadsheet that has sensitive info removed for privacy concerns. As for the formula, I don’t have one myself, just copied and pasted what was given on here. I tried both given and one gave me a “Err:511” and the other “#N/A”.example.ods

[Lupp]

It’s my turn now to not understand. Please edit your question another time and attach a spreadsheet document demonstrating what you have (with formulae) and probably what you want (without formulae, directly entered).
I will see for enough “karma”.

[jimk]

The problem with your formula is that it needs to include the sheet name: =VLOOKUP(B2;$lookup.A1:B192;2;0). Calc can also do this for you, if you click in the formula, then go to the “lookup” sheet and select the cells.

[Lupp]

Couldn’t find the attachment mentioned under “Edit #2”.

[jtyson]

Not sure what happened. I uploaded it again.

[Lupp]

(I assume you don’t actually mean equality by your use of the equal sign.)

Suppose you have a lookup table in (say) L2:M101 assigning the groups entered in column M to the companies contained in column L. Column L of that table (L2:L101) will be used as the range for a Data > Validity setting for cell B2. Cell B4 may then contain the formula =VLOOLUP(B2;L2:M101;2;0) to achieve what you want. There are variants to get better scalability…

Edit 1: Attached this short demo.
Edit 2: Also attach this reworked version of the file provided by the OQ.
Edit 3: Attaching the extended demo announced in my recent comment.

[jtyson]

My comment was too long so I edited my original post

[mariosv]

If the answer solves your question please tick the .

[jtyson]

Once I’ve got it figured out, will do.

[jtyson]

Well thank you I appreciate that. As for your comments, I had redundant sheets listed because that is how the file was sent to me and I simply added the lookup sheet. I’m not at all familiar with these processes, and the drop down menu would only work with that second “client” sheet attached, so I imagined someone formatted it that way.

Now I just need to play with it and see if I can figure out how to stretch the formula you gave me to fit all of our clients.

[Lupp]

@jtyson: What you called “stretch” was supposedly “scale” (up) in my words.
As this is a reoccurring task in spreadsheets as soon as they partly are expected to do database work, I will attach another demo to my answer to demonstrate one of the ways to handle this to some depth.