• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summary data

SAN04

Member
i need to summarize data in one tab from various tabs based on name of tab.

If i mention another date in red cell of "data tab" and there is another tab with same name then data should come in the data tab based on the module name,

Sheet attached for reference.

Thanks in advance !
 

Attachments

p45cal

Well-Known Member
In cell G2:
Code:
=INDIRECT("'" & TEXT(G1,"dd-mmm") & "'!A7")
You will have to copy down then manually change the address of the cell.
When you change the date in row one it should fetch data from the corresponding sheet.
 

p45cal

Well-Known Member
SAN04 said:
Thanks p45cal.....but can we do it with other formula where we shall not require to manually change cell reference
Why didn't you reply here instead of adding a message to my profile?

You asked for it: In G2, copied down and across:
Code:
=INDEX(INDIRECT("'" & TEXT(G$1,"dd-mmm") & "'!$A$1:$F$14"),MOD(INT(ROW()/2)-1,8)+7,INT((ROW()-2)/16)*2+1+ISODD(ROW()))
I hate it.
 

Peter Bartholomew

Well-Known Member
I have been playing with the problem, but using the beta release of Excel 365. The starting point was to define a Lambda function DATAλ
74033
which returns the data corresponding to any given combination of date, line, module and plan/act setting.

I evaluated two alternative formulas to select the data from the appropriate sheet. The first was derived from @p45cal's formula
= INDIRECT("'" & TEXT(date,"dd-mmm") & "'!data"),
whilst the second used CHOOSE,
= CHOOSE(MATCH(date, dateHdr), '21-Mar'!data, '28-Mar'!data, '04-Apr'!data, '11-Apr'!data).

The trick then is to use the headings of the output table to create the values that generate entire columns as a single dynamic array. The basic Lambda function may be written to the formula anchor cell
Code:
= LAMBDA(date,idx,M,A,
      LET(
         line, idx+4,
         column, 2*(XMATCH(M,{"S0","S1","S2"})-1) + IF(A="Plan",1,2),
         mData, CHOOSE(MATCH(date,dateHdr),'21-Mar'!data,'28-Mar'!data,'04-Apr'!data,'11-Apr'!data),
         INDEX(mData, line, column)
      )
  )(@dateHdr,QUOTIENT(MOD(k,16),2)+1,ModuleHdr,Plan∕Act)
but the complexity is hidden by naming the formula (excluding the final parameter string) to give
= DATAλ(@dateHdr,QUOTIENT(MOD(k,16),2)+1,ModuleHdr,Plan∕Act)

74034

Excel solutions don't always have to look like spreadsheets!
 
Top