[Solved] Auto Username:

[JoelP]

Hello OpenOffice Team,

I am looking to have a formula auto fill a cell for me when a particular 4 digit number is enter into a cell.

Here is what I need:

If BG6 equals 7613 then AO8 should read ABC123

If BG6 equals 7614 then AO8 should read ABC456

If BG6 equals 9205 then AO8 should read ABC789

Your help will be greatly appreciated...

Thanks,

[Zizi64]

Use a helper cell range with columns of the the codes and user names, and use the function VLOOKUP() in the cell AO8.
You can use the column of the codes for the "Data validity" function of the cell BG6 (instead of the fixed list).

[JoelP]

Hello,

I am not sure as to how to do what you are saying.

I would much rather stick to simple.

If not, can you do it in my sample and I will look at the formula so I can better understand what you are saying.

Thanks...

[Zizi64]

foeg_DMO--TEST FILE_2.ods

(23.23 KiB) Downloaded 62 times

See the the named ranges on the Helper Sheet, see the Data validity in the cell BG6, and see the function VLOOKUP() in the cell AO8.

[JoelP]

Hello,

I did see how it works, however I do not understand where I can add more formula.

I am a beginner on this and seems like its a bit more than I can understand.

Is there a way you can pinpoint what menu or buttons do I click to get in to add more data so when I select from the Data Validity cell I can get the information auto loaded.

Thanks...

[Zizi64]

You can to expand or redefine the named ranges, and you can add more items to them.
A simple method: insert one or more row/s/ at row 3 of the Helper Sheet...
In this case not needed to change the formulas or the data validity parameters: they will update automatically.

[JoelP]

Hello,

In what cell did you create the list of usernames?

I think this is what is confusing me.

Please advice.

Thanks..

[Zizi64]

In what cell did you create the list of usernames?

Have you seen the Helper Sheet?

The function VLOOKUP can search in the left column of specified cell range, Then it can choose an another data from same row based on the third parameter of the function. The parameter is 2: the second column contains the User names.
(Do you meant: the ABC123, ABC456, ABC789 are the user names?)

[JoelP]

Hello,

I am truly sorry for the back and forth. I am truly loss.

If you can give me like detail how to, I will be able to follow you step by step.

But this way I am getting more loss then I am getting anywhere.

Your patience is truly appreciated and I want you to know that I am grateful so far for what you have done.

Please give me detail how to so I can see as I read.

Thanks...

[Zizi64]

- Click on the tab "Helper Sheet" below of the screen. (activate it)

- Select (highlight) the cellrange A1: A4. That is the named range "DMO_list" (The name is displayed in the name box.) This was used as the cellrange of the Data validity.

- Then select the cellrange A1:B4. That is the named range "Helper_table" (The name is displayed in the name box.) This cellrange was used for the function VLOOKUP()

...or you can use the Sidebar/Navigator/Range names feature to see the named ranges...



Please read the description of the function VLOOKUP() in the Help.

[JoelP]

Hello,

This is truly not working for me.

There got to be a more simple formula that is something like this:

=IF(BG6=7613'AO8;"ABC123") then if BG6=7614'AO8"DEF456") then if BG6=9205'AO8"GHI789")

Please advice. The other way has become to difficult for me to deal with.

Again, your help is truly appreciated...

Thanks...

[Zizi64]

My example file works fine for me in my AOO4.1.2 and LO4.4.7.

=IF(BG6=7613'AO8;"ABC123") then if BG6=7614'AO8"DEF456") then if BG6=9205'AO8"GHI789")

Type (or copy) this formula into the cell AO8:

Code: Select all

=IF(BG6=7613;"ABC123";IF(BG6=7614;"DEF456";IF(BG6=9205;"GHI789";"")))

See the description of the syntax of function IF() in the Help.
In my opinion a nested IF() is not an elegant solution when you want use more than two condition...

[JoelP]

Hello,

I understand what you are saying, but to me I can understand this format better.

Also, I just try to add other numbers and it works fine, however when I try to add If BG6=DMO;"Select Unit #" and it gives me an error when I enter letters instead.

Any possible solution to that?

Here is the exact formula I entered...

=IF(BG6=DMO;"Select Unit #";IF(BG6=7613;"FPH28304";IF(BG6=7614;"FPH4360";IF(BG6=9205;"FPH28306";"")))

Thanks...

[Zizi64]

Any possible solution to that?

The value DMO is a String, but not a number. You need use quotes as Variable Type sign:

Code: Select all

=IF(BG6="DMO";"Select Unit #";IF(BG6=7613;"FPH28304";IF(BG6=7614;"FPH4360";IF(BG6=9205;"FPH28306";""))))

(and four parentheses at the end of the formula)

[JoelP]

Hello,

You have truly delivered helping me and for that I will always be thankful.

I know no one gets paid to help here and that is truly admirable of you to gone this far to help me.

I have now successfully accomplish what I needed with the formulas.

Thanks so much...