Information from table on one sheet, to multiple tables on another?

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

Attached.


Thanks for the tip!

Could you explain what you are trying to do? Many members (myself included) cannot always see .png files, and your sample workbook does not really explain anything.

I have filled in the first 5 tables on Sheet4. The first table is in yellow and I used straight cell references in the formulae. The other tables I used the table nomenclature where I could. The INDEX part of the formula would not accept a relative table reference (at least the way I tried) so the INDEX has a cell reference while the rest of the formulae have table nomenclature.
This way you have a choice of how you want to proceed.

All formulae are ARRAY FORMALAE enter with Ctrl + Shift + Enter.

I will attempt to give an explanation of the formula that I used on Sheet4!B2 that was filled across and then down for the first table. This will be as basic as I can make it for you.
Starting from Left to right in the formula:
=IFERROR(the rest of the formula,"") This is a "trap" for errors. If the formula results in an error, which in this case is most likely to be because the data being sought has run out (no more data) the result will be "" or a null cell. This appears to be blank but really has "" in it.

INDEX(Sheet2!B$12:B$17, this is the range where the data wanted is to be found. INDEX has 2 more arguments of ROW and COLUMN. The ROW argument for INDEX follows the comma and is in this formula "SMALL(IF(Sheet2!$A$12:$A$17=$A$1,ROW(Sheet2!$A$12:$A$17)-MIN(ROW(Sheet2!$A$12:$A$17))+1),ROWS($1:1))" all of which in this formula results in 1 (the first row of data wanted). Notice that the argument for the ROW part starts out with SMALL(IF( which in simplest terms means to get the smallest value IF followed by the condition for IF which is that Sheet2!$A$12:$A$17 be equal to the value in A1. The TRUE value for the IF is "ROW(Sheet2!$A$12:$A$17)-MIN(ROW(Sheet2!$A$12:$A$17))+1)" which returns the values {1;2;3;4;5;6}. The MIN(ROW( part is an assurance that the formula is actually going to return a row value of at least 1 and 0 isn't a possibility. All of this is followed by ROWS($1:1). This is a counter for the SMALL function seen earlier. The first row will be $1:1 the second row $1:2 etc. That gives the smallest value, second smallest etc. The language here is confusing because what is being returned is the FIRST value, the SECOND value etc as the formula is filled down the column.

If you entered the formula with just an enter, the result would be a blank cell because an error would be caused because there are several cells being compared with several other cells and there could be several answers returned. This would trigger the IFERROR and hence a blank cell. An array formula will however process a range of cells against another range of cells to produce a single result and that is what you want.

The formulae in tables 2 to 5 use Table nomenclature replacing all ranges in the formula except for the INDEX part of the formula. If you look at the first formula notice the cell references. Sheet2!B$12:B$17 this means that column B is not locked and when the formula is filled across it will become Sheet2!C$12:C$17. The $12 and $17 lock those values so that they will remain constant as the formula is filled down. The Table nomenclature for Sheet2!B$12:B$17 is Table1[Left] which is logical enough but it will not change to Table1[Right] when the formula is filled across even though this is a "relative" cell reference using table nomenclature, and that is necessary unless separate formulae are to be written.

I hope that this wasn't too confusing and that you are now able to fill in the formulae for the rest of your tables using the form of the formula that you are most comfortable with.

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.