Finding data across multiple sheets

[bsrivatsa]

Hi,

Please see the attached file.

I am trying to populate cells "Calc!D4:F4" with text data entered randomly in cell D4 in the subsequent worksheets. My "if" formula for cell "Calc!D4" is pretty long currently (using indirect- index- Match function) for looking at each cell inthe subsequent sheets sequentially. The formula gets longer for the subsequent two cells. (E4 and F4). I am looking for a simpler solution, as I am pretty sure there would be one! I can't use VBA since the end users will be accessing this file via excel on line.

Thanks in advance,

Bharath Srivatsa

[AliGW]

Sorry - I can't find the current formula. Where should I look?

[bsrivatsa]

I didn't rewrite the formula for this help file. I didn't post the original file as it is too cumbersome and has sensitive data. However, here are the formulae from the original file for the three cells (These formulae work for me)-

1st cell-
IFERROR(IF(INDEX(CZ_Jan!R:R,MATCH(Jan!AA5,CZ_Jan!Q:Q,0))<>"",INDEX(CZ_Jan!R:R,MATCH(Jan!AA5,CZ_Jan!Q:Q,0)),IF(INDEX(CS_Jan!R:R,MATCH(Jan!AA5,CS_Jan!Q:Q,0))<>"",INDEX(CS_Jan!R:R,MATCH(Jan!AA5,CS_Jan!Q:Q,0)),IF(INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0))<>"",INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0)),IF(INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0))<>"",INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0)),IF(INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0))<>"",INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0)),IF(INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0))<>"",INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0)),"")))))),"")

2nd cell-
IFERROR(IF(AND(INDEX(CZ_Jan!R:R,MATCH(Jan!AA5,CZ_Jan!Q:Q,0))<>AH5,INDEX(CZ_Jan!R:R,MATCH(Jan!AA5,CZ_Jan!Q:Q,0))<>""),INDEX(CZ_Jan!R:R,MATCH(Jan!AA5,CZ_Jan!Q:Q,0)),IF(AND(INDEX(CS_Jan!R:R,MATCH(Jan!AA5,CS_Jan!Q:Q,0))<>AH5,INDEX(CS_Jan!R:R,MATCH(Jan!AA5,CS_Jan!Q:Q,0))<>""),INDEX(CS_Jan!R:R,MATCH(Jan!AA5,CS_Jan!Q:Q,0)),IF(AND(INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0))<>AH5,INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0))<>""),INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0)),IF(AND(INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0))<>AH5,INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0))<>""),INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0)),IF(AND(INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0))<>AH5,INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0))<>""),INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0)),IF(INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0))<>"",INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0)),"")))))),"")

3rd cell-
IFERROR(IF(AND(INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0))<>AH5,INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0))<>AI5,INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0))<>""),INDEX(ML_Jan!R:R,MATCH(Jan!AA5,ML_Jan!Q:Q,0)),IF(AND(INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0))<>AH5,INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0))<>AI5,INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0))<>""),INDEX(JA_Jan!R:R,MATCH(Jan!AA5,JA_Jan!Q:Q,0)),IF(AND(INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0))<>AH5,INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0))<>AI5,INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0))<>""),INDEX(AH_Jan!R:R,MATCH(Jan!AA5,AH_Jan!Q:Q,0)),IF(AND(INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0))<>AH5,INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0))<>AI5,INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0))<>""),INDEX(JE_Jan!R:R,MATCH(Jan!AA5,JE_Jan!Q:Q,0)),"")))),"")

Thanks

Bharath

[menem]

If you allow to use helper cells.

C1 (copy down)
=INDIRECT(B1&"!D4")

D4
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($C$1:$C$6)*1/(C1:C6<>0),1))

E4
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($C$1:$C$6)*1/(C1:C6<>0),2))

F4
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($C$1:$C$6)*1/(C1:C6<>0),3))

Regards.

[Bo_Ry]

Please try dynamic array spill at D4

=TRANSPOSE(FILTERXML("<b><a>"&TEXTJOIN("</a><a>",,CS_Jan:JE_Jan!D4:H15)&"</a></b>","//a"))

[bsrivatsa]

Thank you both- @menem and @Bo_ry.

Both solutions that were provided work, however it appears I made an error by not clarifying my request adequately. I apologize for the same.
Please see my updated help request file. I need the column entries in the subsequent tabs to populate the rows in the Calc! tab.

Thank you again for your invaluable help

Bharath

[bsrivatsa]

Any help? Please see my revised upload.
Thanks.

[menem]

How many sheets that you plan to use ?

Regards.

[bsrivatsa]

How many sheets that you plan to use ?

Regards.

I am building a work schedule calendar. Currently for 7 teams, so that is 7*12= 84 sheets. I would like the formula to have the flexibility of accomodating more (or less) teams by just changing the tab reference range.

Thanks.

[Bo_Ry]

Please try

=IFERROR(INDEX(FILTERXML("<b><a>"&TEXTJOIN("</a><a>",,CS_Jan:JE_Jan!D4:H15)&"</a></b>","//a"),SEQUENCE(31,3)),"")


or
Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.

Change file paht in Blue

Please Login or Register  to view this content.

[bsrivatsa]

Thanks for your kind reply.

The solution that you sent using the above formula (=IFERROR(INDEX(FILTERXML("<b><a>"&TEXTJOIN("</a><a>",,CS_Jan:JE_Jan!D4:H15)&"</a></b>","//a"),SEQUENCE(31,3)),"") ) is not returning the right answers. The solution with the power query editor seems to be right. I will try to adopt that in my file and try, though it looks too complicated for me!

Thanks again.

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[menem]

In this case, I can only use helper column (equal to sheets) to perform data into calc sheet , then populate them to answer area.

Prepare (retrieve) data to calc sheet.

Define how many blank row in data sheet & column name at
I3 = 3 (Mean 3 blank rows in every data sheet)
J3 = D (Data in column D)

Define index id of populate table (start from 1 to total sheet +2)
Note : I have l.margin & r.margin for easy insert to support more data sheets.
Start at I4 to P4 (1 to 8).

Enter sheet name in J6 to O6.
J7 (copy to end of the area)
=INDIRECT("'"&J$6&"'!"&$J$3&$I$3+$C7)

* Insert more column if need (and don't forget to rebuild
Col.ID from 1 to n+2).

In answer area enter 1, 2, 3 as number of data to show at D5 to F5

D7 (copy to end of answer area)
=IFERROR(INDEX($I7:$P7,1,AGGREGATE(15,6,$I$4:$P$4/(1/($I7:$P7>0)),D$5)),"")


Regards.

Note : Another way maybe VBA (UDF).

[bsrivatsa]

Open blank query in the editor, launch Advanced Editor and paste in the following code.

Hi Bo_Ry,

The third column disappeared (i.e., I have only columns 1.1 and 1.2 in the output table) after I pasted the abovementioned code after changing the filepath.

Any thoughts? Again thanks for taking the trouble to solve my problem.

Bharath

[bsrivatsa]

In this case, I can only use helper column (equal to sheets) to perform data into calc sheet , then populate them to answer area.

Prepare (retrieve) data to calc sheet.

Hi,

Thanks for this solution. This works for me. I have two questions-
1. Is there a way for the column IDs to autocorrect when adding or deleting columns? (Currently, I have to relabel them).
2. When I insert a new column between the L. and R. Margins, the cells are blank and don't copy the formulae from the adjacent left column automatically. Is there a way to autopopulate the formulas? I tried inserting a table using the data range, yet the formulas didn't autopopulate when I added a column.

Thanks again for this solution.

Bharath

[AliGW]

@ bsrivatsa

You have been asked NOT to quote unnecessarily, yet you continue to quote entire posts. Please stop doing this! Thank you.

[menem]

It can't be auto labeling or filling formula.
But you can pre define all 84 columns (and name
of all sheets) between L&R margin even that sheet not present.

Regards

[bsrivatsa]

Well, I did that because my response was not to the immediate prior message in the thread. Looks like I am misunderstanding you. Anyhow, sorry for the inconvenience. I will stop quoting.

[bsrivatsa]

@menem. Thanks. I understand.