hm...



I need help with a very special formula, that confuses me to no end.

In the attached file you can see the data sheet. This is the source for everything I need on multiple work sheets.

I need to get the ID based on the value of Age in a between format.
For example: if age is between 10080 and 20160 I need to get the ID of that row in work sheet 1

After that I can use simple vlookups to get the rest, but I always get N/A, SPILL or other errors when trying to use IF, AND and VLOOKUP in conjunction.

I hope my meaning is discernible.

Edit: What makes it hard is that the data in the "data" sheet can be quite long (up to 12000 rows) and I want to auto-generate a new list when exchanging the data in that sheet.
I'm not fixed on using vlookup, but I am fixed on avoiding VB.

Edit 2: I can add and change stuff in the "work" sheet. I can even add entirely new sheets if needed. In the end the most important ones will be the sheets discerning between "age" ranges from data. So in effect it should be the same formula only using different numbers.

Kind regards and thanks for the help in advance,
Jack

Attached Files

Test.xlsx (12.0 KB, 5 views)

Hi could you please add some expected results to your xheet?
What is the expected result ID in this case?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

hm...

Of course, I added the explanation and expected results.

Edit: The list should end, once all ID values have been written into it, if the AGE value is between 10080 and 20160.
Edit 2: The expected result is that if the value of AGE is between those 2 numbers, the ID in that row will be copied to the "work" sheet.

Thank you!

Attached Files

Test.xlsx (12.0 KB, 6 views)

Hi, see in attached file a possible solution...

Attached Files

Test2.xlsx (12.8 KB, 6 views)

hm...

The only drawback I see is that I won't get a list without spaces.

As I said, once the list from the values in "data" "ID" is generated under "work" I could use VLOOKUP to get the other values I need from "data", since ID always is a unique value.

But this looks closer to the thing I want from any of my tries.

another solution...

Attached Files

Test3.xlsx (14.7 KB, 4 views)

Attached is an example of possible solution using worksheet functions only.

Columns in Tables with differently colored headers are helper columns, and probably you prefer to hide them from users;
I had to format datetimes in data table differently, and to edit some dates, as with my regional settings Excel read them as strings, or switched months and days. Probably you have to reformat those columns in your format (and check dates);
As in helper column of Report (DataRow) are present row numbers of matching rows of data Table, I advice to use INDEX() instead of e.g. VLOOKUP() to read all info from data Table - I think it will be somewhat faster;
User can not enter any info directly into Report Table, because whenever user sorts the data Table in different order, all Report data row positions probably will change, but any manually entered information remains in old position - i.e. will be attached to wrong row. So either you remove columns Progress and Feedback, you enter this info into data Table, or you will have a separate Table, where you enter this info with matching ID, and report reads it from there;
You can't have such reports without having at least some empty rows at bottom (the safest way will be having at least as much rows for Report as there is in data Table). You can always set an autofilter e.g. for ID column being not empty (don't forget to refresh the filter whenever some data are added);
In case having empty rows in Report will be a problem, you can use ODBC query from data Table instead of using worksheet formulas (using StartAge and EndAge fields as query parameters). You can set the query to be refreshed automatically whenever any parameter field is edited, and whenever the workbook is opened. And you also can refresh the query manually whenever you need it.

Attached Files

ReportExample.xlsx (18.8 KB, 6 views)

hm...

This looks like the best basis to work with. But I can't follow the way the formulas work.

Would I need to adjust the helper columns in data manually?
The data that will be copied there never changes when it comes to columns, but the rows can vary wildly to up to 12.000.

I would use the "Report" sheet as the basis to create the sheet where the "feedback" and "progress" can be added as that is the most important part about the entire file.

In that case I only need to generate multiple report sheets for each "age group" (ages 10080 - 20160, 20161 - 30240, 30241 - 43200, 43201 - 64800, above 64800).
This sheet would only need to contain the "ID", as it only serves as a helping sheet to filter data by "age".

The only formula I have a tenuous grasp on is the one in RepRow, but nStartAge and nEndAge throws me off. I know that it refers to the values in "Report", but I don't know how.
I need to understand it, so I can create the sheets where users can enter their feedback and progress.

Thank you fjns and ArviLaanemets for the great work!

I don't see any expected results..
If the value of AGE is between two numbers, which ID should be returned??


PS I hope your cold is getting better ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

hm...

Every "ID" should be returned where "age" is between the 2 numbers. I know how to get the rest of the information automatically. The number of rows in "data" can vary a lot.

So in essence I will create sheets for every "age"-group I need (most are between 2 values, 1 is above 64800). In essence this creates a filter by "age"

I don't have a cold

I used Defined Tables feature (to define such Table, activate any single cell of general table, and from Insert menu, click on Table icon). With Defined Tables, you can use specific syntax to refer to various components (Table, Column, Header row, a cell in same row) of such Tables. The formulas itself are common Excel formulas. E.g. tData[RowNo] refers to column RowNo of Defined Table with name tData, tReport[[#Headers];[ID]] refers to header of column ID of Table tReport, and [@Owner] refers to cell in same row of Table where the formula origns from in column Owner. You can see/edit properties of single Table when you activate any cell of Table, and open the menu Table Design. And you can see and edit all Defined Tables from Name Manager of Formulas menu too.

I also used named ranges (nStartAge, nEndAge) - them you also can see and edit in Name Manager.

Defined Tables work generally like Dynamic Named Ranges we could use earlier, plus some additional features, like automatic expanding of formulas, formats, and validation rules whenever a new row is added to Table, and automatic correction of all formulas in workbook, whenever any Table Element is changed (e.g. you rename Table tReport as tAgeReport, and in all formulas the reference to tReport is automatically replaced with tAgeReport).

About Progress and Feedback - add columns for them into data Table, and enter them there! When you want to enter them on separate sheet, then the ID for them MUST be entered manually - otherwise you can't guarantee that this info remains linked with right ID.

And you don't need different reports for different age groups. You enter another StartAge and EndAge, and you get report for another age group on same sheet! In case you want to limit possible StartAge and EndAge pairings (Age Groups), add a sheet where you define such group (E.g with columns AgeGroup, StartAge and EndAge, instead current fields for StartAge and EndAge create a Data Validation List with AgeGroup column as source, and based on selected AgeGroup calculate Names nStartAge and nEndAge directly in Name Manager. User selects AgeGroup, and gets the report for this AgeGroup.

hm...

Ah, thank you for the explanation, I can work with that.

The data table is generated automatically by a software and includes many more ages, which aren't relevant to the guys working with the file (hence my question about the 2 helping columns).
That's why we need the filter in the first place.

I thought I'd be able to fetch the "ID"s from "data" with your method, generate a sheet with the additional info fetched directly from data via INDEX or VLOOKUP (since "ID" is unique) where the people can enter their progress and feedback.

Thank you, so so much!

I updated my example, to give you easiest and error-free way to manually add missing ID's to user comments table.

Added is a sheet AgeGroups, where all possible age groups are defined (EndAge is defined for age group ">64800" too, simply it must be big enough - because this makes filtering the Report easier. And there is additionally an AgeGroup "All" defined, which allows to display in Report all data from Data sheet. The column AgeGroup contains group ID, and you are free to edit it in any way so long the value remains unique;

Added is a sheet Comments, where user(s) can enter the info about progress and feedback for any ID. User can enter missing ID's manually directly here, but there is a way to do this in bulk using Report Sheet now;

On Report sheet, now instead of entering Start and End Ages, you select (there is a Data Validation List defined) the Age Group (nStartAge and nEndAge for selected AgeGroup are calculated - check Name Manager to see how);

On Report Sheet, Progress and Feedback are read from Comments sheet, when there exists an ID in this row. When not, those cells remain empty. When ID exists in Report sheet, but doesn't exist in Comment sheet, a message (currently the message is "ID missing!", but you can change it) is displayed;

Now, when you set the Age Group for Report to "All", and Autofilter for either Progress or Feedback column to missed ID message text (currently "ID missing!"), you get a list of all data which don't have ID registered on Comments sheet. Copy listed ID's, and use PasteSpecial.Values (NB! You have to paste values, not formulas!) to paste all missing ID's into ID column of Comments table. Now clear the Autofilter (and set AgeGroup when you don't want all data displayed). And you (or anyone) can now enter Progress/Feedback info for added ID's in Comments sheet.

Edit: Btw, age 10079 falls out of age ranges you did declare in post #8!

Attached Files

ReportExample.xlsx (22.8 KB, 6 views)

hm...

This is absolutely perfect (and idiot proof), thank you for all the work you did.

Ages below 10080 aren't relevant for our purposes, that's why I don't mention them, even though they are part of the data sets that get copied into the file.

I just duplicated the tables to set to a single age group, so I have one report and one comment sheet for each age group. That way comments, progress and ID always match up.

Thank you again!

Kind regards,
Jack