Ask Your Question

How to create a custom formula?

asked 2017-12-30 10:10:42 +0200

jtyson gravatar image

updated 2018-01-03 00:50:05 +0200

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.


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".C:\fakepath\example.ods

edit retag flag offensive close merge delete


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".

Lupp gravatar imageLupp ( 2017-12-31 11:38:46 +0200 )edit

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.

Jim K gravatar imageJim K ( 2018-01-01 21:53:37 +0200 )edit

Couldn't find the attachment mentioned under "Edit #2".

Lupp gravatar imageLupp ( 2018-01-02 21:04:41 +0200 )edit

Not sure what happened. I uploaded it again.

jtyson gravatar imagejtyson ( 2018-01-03 00:50:46 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-12-30 12:15:19 +0200

Lupp gravatar image

updated 2018-01-06 14:09:06 +0200

(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.

edit flag offensive delete link more



My comment was too long so I edited my original post

jtyson gravatar imagejtyson ( 2017-12-31 11:30:14 +0200 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2018-01-03 00:05:25 +0200 )edit

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

jtyson gravatar imagejtyson ( 2018-01-03 00:52:07 +0200 )edit

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.

jtyson gravatar imagejtyson ( 2018-01-06 05:45:58 +0200 )edit

@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.

Lupp gravatar imageLupp ( 2018-01-06 14:07:04 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-12-30 10:10:42 +0200

Seen: 553 times

Last updated: Jan 06 '18