Nov 14 2018 11:34 PM
Hi there,
Can someone please help me ? I have a workbook with 45 sheets. Each sheet corresponds to each week of the year. In other words I will end up with 52 once the year is finished. Now within each sheet there is a dashboard (see Capture 2) which contains a specific value in the same cells (M14 - M16) throughout all 45 sheets. I need to plot these values in a new sheet in three different rows each in a new cell by each corresponding week (see Capture 1) S1 = Week 1, S2 = Week 2 and so on until Week 45. Thank you.
Regards,
Eddie
Nov 15 2018 04:28 AM - edited Nov 15 2018 04:30 AM
have you tried using "Consolidate" command in data-->data tools? it helps you create a single sheet consolidating all the data in seperate sheets. You need to create names of these ranges on seperate sheets first than consolidate all these names in to one page.
Alternatively you can use indirect to get the data you need. Create a table with sheet names and cell addresses like sheet names in A, and cell name in B column, and use the cell and use the information in a indirect formula like:
=INDIRECT("'"&1A1&"'!" &B1)
power pivot can also help but I do not have experience in it.
edit: typo
Nov 15 2018 04:38 AM
Thank you for your response. I actually got it to work with the following formula =INDIRECT("'ReuniónS("&ROWS(D$3:D3)&")'!M15") but the only thing is it works listing the values in a column. I really need it to list them in a row but it repeats the same value across the row. See attached.
Nov 15 2018 04:57 AM
SolutionThe ROWS(D$3:D3) will not produce any different result in seperate columns if you copy the formula in the same row. You can try using COLUMNS instead if you are spreading this formula in columns.
Nov 15 2018 05:15 AM
That's what I thought as well so I did =INDIRECT("'ReuniónS("&COLUMNS(F$13:F$13)&")'!M15") but I still get the same result "4".
Nov 15 2018 05:17 AM
change it to: COLUMNS($F$13:F$13)
Nov 15 2018 05:26 AM
Yes that did it !!! Thanks alot for your help !!!
Nov 15 2018 04:57 AM
SolutionThe ROWS(D$3:D3) will not produce any different result in seperate columns if you copy the formula in the same row. You can try using COLUMNS instead if you are spreading this formula in columns.