Excel Formula for database

[May_ThuThu_Aung]

Hi,

I would like to know the formula name to link with dropbox and the "IF" function. For example, when I select a name from one cell I would like to appear the respective data of that name in another cell. Could you please show me how to link and which formula should I use for this data analysis. I'm looking for your reply. Thank you in advance.

Best regards,

MTTA

[#2]

Hi!
It is not clear what you need. Can you please explain in more detail?
It looks like VLOOKUP or XLOOKUP might be the formula you need.
What do you mean by Dropbox? Are you referring to a dropdown list?

[#3]

HI @Celia_Alves 

Hi, MS-Tech community,

Thank you for your reply.

Yes, what I mean a dropbox is a drop-down list.
I need to link with the drop-down list and formula. Is it formula working in drop-down lists?

Let me explain in brief, for example, I have 3 categories in 3 columns which are A, B, and C. When I type A1 in A column, I want to appear the data of A1 in B and C columns with drop-down lists because of the need to choose the data from that lists. Next, when I change A2 and then the A2 data would appear in those columns the same as above.

I'll prepare the data of A1 in another sheet to use for the drop-down list. Is it possible to link with drop-down function and formula? or which function/formula should I use for this data analysis. I need help to solve this.

Sorry for my explanation to make you confused. Excuse my English writing. I'm looking for your reply. Thank you so much.

[#4 · Accepted solution]

@May_ThuThu_Aung, it is a bit confusing but I think you will find the answer in the links that I will provide below. Please let me know if that solved your problem. If you still need help after that, please answer back sending a sample file and I'll try to help further.

video: https://www.youtube.com/watch?v=VxReJslyJm8 (see more information on the video description.)

Playlist with other examples: https://www.youtube.com/playlist?list=PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y

[#5]

@Celia_Alves  Thank you so much for sharing the link. It is really helpful. After watching the video link, I was too close to the answer but still have errors. I would like to raise the errors which were found when I did according to the tutorial.

I've made with 2 separate sheets one is "References" and another one is named with "All Contact". In the references sheet, data under the blue table is for "District" and the black is for "Township". When I create drop-down lists and formulas its appears "#VALUE!" for some cells. I can't fix them and how to edit the formula. Except for these errors, all are looking OK so far.

Please kindly see the attached file. Thank you so much again.

[#6]

@May_ThuThu_Aung 

I amended the file and all seems to be working now. You just need to complete a table with the table codes as explained in the tutorial attached.

I used a simpler formula for the names and the data validation.

I hope this helps! Good luck and let me know how it goes.

[#7]

Hi @Celia_Alves 

Please accept my apology for the delayed reply because I was on-duty traveling start from this week until today. I've looked at the files of tutorial and database which you sent, they are very helpful for my database and I've learned new formula from you. And then, I completed the table as you explained, now I'm working on this but not yet found the error. I'll take time to fill the data if it is ok I'll start using for the entry process.

May I ask you one thing, how did you do this formula. I've tried this but not yet succeed.

Thank you very much again for your help @Celia_Alves . Have a nice day.

[#8]

@May_Thu Thu-Aung
Hello! I am glad that you made progress.
Please feel free to use that file that I sent you for your work if that helps you.

That formula is the way Excel writes formulas when we are working inside tables. The square brackets and @ sign are put by Excel when we refer to a cell in another column of the table and in the same row where we are righting the formula.

Tables is one of the most important things one should know in Excel.

I recommend this video about Excel Tables.
Starting on minute 10:35 it explains a bit about formulas in tables, but I recommend watching the entire video if you are not familiarized with tables.
https://youtu.be/Qk7bChbIefg

If you use an older version of Excel, you may get in your formula "[This row]" instead of "@". The following video explains this and a few note very useful things that we should know about using tables and formulas inside of it:
https://youtu.be/tTYQrk3hSYo

I hope this helps! Good luck!

[#9]

@Celia_Alves 

Hello! @Celia_Alves  all the videos and knowledge that you shared were very useful. Thank you so much. I'll share it with my colleagues for learning development. They let me pass their big thanks to you. Have a nice weekend. See you later. :D

[#10]

I am glad that I could help and happy to see your commitment to keep learning and share knowledge with your colleagues. That's the best way to go.
All the best.

[#11]

HI @Celia_Alves 

How are you?

Now I'm working on the database and I always thank you for helping me.

I'm here to need your help again. It is about the database also the same one. I was OK with the previous database that you help me before in formula creating and I'm going to start the data entry process very soon.

But, I got one more issue that in each of the districts have respective townships. I was trying to link with "Districts" and "Townships" to avoid to choose the townships from the long lists. I want to change the Townships name depends on the Districts that I chose. I was out of the idea of how to and have tried in the database. After all, I've no idea and wrote this request to you. Please kindly see my working file so far.

I was wondering if you could help me to solve this error again. Please accept my apology for bothering you again. Thank you so much.

The attached file of My Data_Version1 is completed with combine township and version2_error file is my working file so far.

Best regards,

May Thu

[#12]

@May_ThuThu_Aung 

Your file is getting quite complex and would probably need deeper attention than the one I can offer right now.

Similar work to the one that I made on sheet References has to be done. 

RegionCodes table needs to be duplicated on a DistrictCodes table.

A table with the townships for each district needs to be created and named according to the code determined on the DistrictCodes table.

The data validation rule for the Townships column needs to lookup the district name in the new DistrictCodes table and find the corresponding table of townships for that district.

I have done some work for you.

On the table RegionCodes you can delete the column Townships Table if you don't need those Township tables by region. Instead, you need to build as many tables as districts that you have with the Townships that belong to each district. I already made one table as example for Hinthada District.

The formula in the named range "Townships" was amended.

On the All Contacts sheet, you can see that The last row is working well according to the Hinthada Townships table that I created.

Now you need to:

- complete the list of all districts

- give each one a code in column 2 of the Districts code table

- build all the different tables for each district with the corresponding townships and name them according to column 3 of the Districts code table

I moved all the tables with townships by region below. You can use them to copy the names you need for each new district list of townships and then you and delete all the black tables with townships by region.

I hope this helps. Please see file attached

[#13]

Hi@Celia_Alves 

How do you do?

Well received your reply and sorry to get back to you late because of an internet connection problem. Start from this week we are working from home to protect Covid-19 and we have prepared remote working set up until the end of this month and maybe the end of April. 

I've looked at the database but not testing on my own yet. But it looks OK and accepts my apology for making you confuse with my database. Thank you so so much for your help so far, without you I can't make it by myself. You are a good contributor for me through this Microsoft Tech Community. I didn't expect that I could find someone to help me and my database. Thanks so much again.

How's everything in your country? Is everything fine? Please special take care of yourself and your family. 

Have safe and healthy days with you and your family.

Yours sincerely,

May Thu :D

[#14]

Hi, May!
Thank you for your kind words. Here, all is well; I am also working from home. I hope you are well and safe too.
I hope that you can continue your database work using the file that I sent you. If you are not sure how to continue, please feel free to reach out.
Stay safe and take good care.
Celia

[#15]

please help with this

Enter a database function in cell K18 to determine the total number of FT employees. To complete the function use the range A5:H25 as the database argument, cell E5 for the field, and the range K10:K11 for the criteria.