0

I am having two tables, one table is main data, separated into many columns and blocks per 10 rows in one, this is fixed. In Column A are unique numbers that will be used in drop down.

Table 1

Table 1

On second table is a report or printing sheet where I would like to have all infos from table 1 when choosing from drop down list from Column A2. To populate all matches, starting from Column B, no matter if there are couple matches or if it is complete, means there are blanks too, but withing those blocks of 10 rows.

Table 2 Table 2

I have been using this formula but I am not getting proper result, or it matches only for first row and rest is or repetative or error.

=INDEX(Table1[Datum];SMALL(IF(Table1[Nummer]=Printable!$A$2;ROW(Table1[Nummer])-ROW(INDEX(Table1[Nummer];1;1))+1);1))

Data added!

Normal vlookup is also starting point but I got lost with everything and simply cant get all required matches..

Number  Date        Signature  1. Problem   2. Description  3. System
ÄM11_1  01.01.2021  MV         Alarm ON     Alarm OFF       0528
        01.02.2021  AB         N/A          N/A             0347
        10.02.2021  CD         Pressuure H! Valve           0321

... this is for one unique number (column A), ten rows are per one block (same as on screenshot divided with line)...doesn't have to be full but must be 10 rows!

Edited! Output should be something like this...colored field is drop down, and when I choose something from it, all fields should be updated with the data from main table...everything at once.

Output

8
  • Can you paste (some of) the table data, which is text in the first place, as text and not as a screenshot? Apr 22, 2021 at 15:13
  • @SaaruLindestøkke From main data table or?
    – MmVv
    Apr 22, 2021 at 15:41
  • I don't know, you're best to judge that. Something small that answerers can use to reproduce your issue and provide a solution. Apr 22, 2021 at 15:43
  • It would also help if you show what the intended result is, e.g. by manually creating a small version (i.e. 5 rows) of your desired resulting table. Apr 22, 2021 at 15:44
  • @SaaruLindestøkke I added some values....well think of it as lookup values that have to populated from one big table with unique values, to match all what it finds depending on drop down.
    – MmVv
    Apr 22, 2021 at 15:52

2 Answers 2

0

As you goal seems to be to easily access a set of rows based on the "Number" column (without having to scroll), I think using tables with a filter is the most convenient:

Final result of filtering table

To achieve above result you first need to make sure that the "Number" value appears in every row. You can do that with the formula =IF(ISBLANK(B2), A1, B2):

enter image description here

Then copy column A and paste it in place, but paste it as values:

enter image description here

Now you can safely delete column B (where only every 10 rows the number is written).

Last thing to do is to select all rows (including the header) and press CTRL+T. This creates a table out of it, allowing you to easily filter.

2
  • Hmm interesting approach, but what if I have more tables beneath? I am saying this because I just saw that it might be possible to have many columns that can't fit on regular printed sheet, so the table must be cut down into half or more. That means I can have more excel tables but I might loose filter for other not connected tables...if you understand what I am saying. I wrote above "printing sheet" but page has to be like in a new screenshot I uploaded....
    – MmVv
    Apr 22, 2021 at 18:13
  • You could create multiple, separate tables below each other, and only filter the top one. Apr 22, 2021 at 18:34
0

The point is drop-down list works when all entries of say AM11_1, must be repeated down the column. Then only it works for that many rows.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .