Database for Languages, multiple values lookup

[JoshMD1]

Hello Everyone!!

I am into learning languages and using spatial repetition. Although there are apps that can do this, I would rather have an excel spreadsheet. I created one with worksheets for each of my language. I would like to have the HOME page as a place where I can see all the material that I need to study based on today's date. In my Russian worksheet, I have listed the books/apps that I want to use, and I have been putting the dates based on the spatial intervals that work for me, but I get confused trying to use VLookup to find the information (look for todays date in columns D to Column O) and list the exercise I should do (Column B) on the home page under the respective language. In column U, I was able to do a vlookup for a single column, but I am confused how to list the appropriate lessons on the home page without spaces. I would greatly appreciate any help with this because I have spent a few hours on youtube, and I am still confused

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[JoshMD1]

Thank you for the help! I am new at this site. I made the changes you suggested and reuploaded!

[JoshMD1]

I only just started, but each sheet may well end up with 2000 or more entries eventually. Over that span, there can be many overlaps of the same date anywhere in that particular sheet (language) and other sheets (languages). I was hoping to have the home page automatically show everything that has the date I am looking for in all sheets (each containing possibly 1000s of cells of different learning materials. I could create a table for each sheet or language and search by date, but I was hoping to avoid having to click through many sheets just to see whats due each day.

[JohnTopley]

Given your reply in post #4, you need to think very carefully how you organise your data.

Your posted file potentially gives a misleading impression of the volume and type of data. The ideal in Excel to have all data one sheet and/or a series tables which can easily be cross-referenced: for example, a table of your books/learning material cross-referenced to a language(s).

Even with your simple example is not straightforward to get your data by date.

[kvsrinivasamurthy]

Pl see file.
ARRAY formula in E7 then drag across.
Formula is suitable for format of data in German Sheet. Result will be "YES".

Please Login or Register  to view this content.

[JoshMD1]

Pl see file.
ARRAY formula in E7 then drag across.
Formula is suitable for format of data in German Sheet. Result will be "YES".

Please Login or Register  to view this content.

Thank you very much for helping me out with this! I am actually trying to put the names of lessons (currently in D8-10 on the Home page) to actually show up under the respective languages (instead of YES). I created a video that I hope explains it better (but I apologize for the audio because it is terrible, and I had to use my laptop mic).

youtu.be/_meaV0UZ61w



Originally, I thought I would do this by:
1)creating a formula in each worksheet on the side that will take a)look at the table by row for today's date b)if it sees todays date in that row, it will list the contents of cell B in that respective row. Unfortunately, that would end up giving me a lot of gaps, so....

2) After that, I was hoping that in the Home sheet, I can put the values from the side table (what I mentioned above that will have a list of cell B items from each sheet that align with the correct date) to a list without gaps on the home sheet under the respective languages.

Thanks again JohnTopley for answering. I can see that my goal is a little complicated. My eventual goal is to create a worksheet I can use for all types of spatial repetition in learning (not just languages). The benefit being I can have a central area to see all the lessons for languages, school classes, etc that I need to repeat in time intervals that ensure my brain can remember 90% of the material for years).

[JohnTopley]

Looking at (part of) your video I am even more certain you need to give more attention to the design of your database.

If you not get this right, then you potentially will end with very complex formulae to unravel a poorly designed DB.

For example, as a general rule having separate sheets for components of "Category" (Category: Languages, Components: French, German, Russion ...) is to be avoided.

A sheet with Category "Languages" containing ALL language data is much better.

Others would advocate a sheet with all data (with columns for Categories, sub-categories) but in my view is this might not be (but do not preclude it) the best option in your case.

Perhaps consider using Access as your DB product.

[jackabs333]

Looking at (part of) your video I am even more certain you need to give more attention to the design of your database.

If you not get this right, then you potentially will end with very complex formulae to unravel a poorly designed DB.

For example, as a general rule having separate sheets for components of "Category" (Category: Languages, Components: French, German, Russion ...) is to be avoided.

A sheet with Category "Languages" containing ALL language data is much better.

Others would advocate a sheet with all data (with columns for Categories, sub-categories) but in my view is this might not be (but do not preclude it) the best option in your case.

Perhaps consider using Access as your DB product.

Thanks John. I'll look into it database. You were right earlier that my example doesn't show the breadth of how much will be added. But thinking ahead, I could easily have 500 entries for 10 different languages. If I have them all on one sheet, I don't feel if be able to track my progress as well as having different sheets based on language. But I see your point as many excel examples just use a huge database on one sheet.

I guess my question is then, if I put all the material on a single sheet, can I still create the equation I mentioned in the video (if statement for each row containing date I need to show materials under the HOME page in the appropriate language section that I want)?

[JohnTopley]

Your video sound was too poor for me to follow exactly what was required.

In considering what to do, separate collection/organisation of "raw" data from "reporting"/"queries": do the former correctly then the latter is usually straightforward.