• 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.

Replicating formula across worksheets

JDC8457

Member
I have workbook with multiple sheets that are all formatted identically. Basically, each page contains individual employee data.
I created a summary sheet that captures essential data from the each worksheet.
Everything works great on the summary sheet but I cannot determine how to change the sheet reference "S1, S2, S3..." so that I can simply drag the formula down a row and have the formulas change.

Here's the formula...
{=IFERROR(INDEX('S1'!$F$3:$F$17,MATCH(FALSE,ISBLANK('S1'!$F$3:$F$17),0)),"")}

Any help is greatly appreciated.
TIA
 
You should be able to simply change the 'S1' portions in the formula to the appropriate sheet name. Linking a file for us to view would be helpful
 
Yes, I understand I can change the sheet reference. I could save a lot of time if I can drag the formula down a column and have the sheet reference change automatically.

Unfortunately, my workbook contains confidential personal information that I cannot share with anyone, even excel ninjas.

Thanks.
 
There are formulas such as
{= CHOOSE({1;2;3}, Sheet1!dataRow, Sheet2!dataRow, Sheet3!dataRow)}
that will return an array comprising a row (with the locally scoped name 'dataRow') from each of the sheets.

Alternatively you could have a helper range with a relative 'k' referring to each index in turn could be copied down and the sequence k would not necessarily have to be in order.
= CHOOSE( k, Sheet1!dataRow, Sheet2!dataRow, Sheet3!dataRow)

A volatile function that would also work is INDIRECT.
=INDIRECT( sheetName & "!dataRow" )
where 'sheetName' is a relative reference to a list of sheet names (in any order).
 
Maybe horizontally (copy to right)
Code:
=INDEX(INDIRECT(B$1&"!$F$3:$F$17");MATCH(FALSE;ISBLANK(INDIRECT(B$1&"!$F$3:$F$17"));0))
or vertically (copy down)
Code:
=INDEX(INDIRECT(M1&"!$F$3:$F$17");MATCH(FALSE;ISBLANK(INDIRECT(M1&"!$F$3:$F$17"));0))
 

Attachments

  • JDC8457-navic-42010.xlsx
    10.6 KB · Views: 4
Back
Top