Macro to auto place formulas

**Wskip49** · 03-16-2011, 05:27 PM

I have a workbook that keeps track of extend day payments. I am trying to develop a report to keep from printing almost 300 individual sheets. The formulas are based on tab names. I have a macro to name the tab by typing the person’s name in A2 on the session pages. The Extended Day Report tab is where I am trying to get a formula or another macro to auto place in the appropriate cells. Please see the attached file.

**JBeaucaire** · 03-16-2011, 06:41 PM

In C2, then copied down:

=IF(INDIRECT("'" & $B2 & "'!$H10")<>"","Yes","No")

In D2, then copied down:

=IF(INDIRECT("'" & $B2 & "'!$H11")<>"","Yes","No")

...etc.

Once you've created your formulas C2:K2, just copy them down as far as needed for the names you've placed in column B.

**MarvinP** · 03-16-2011, 06:42 PM

Hi Wskip49

Find the attached that will put all session tabs on the last tab in the workbook and give you what I think you want. Welcome to Pivot Tables.

**Wskip49** · 03-16-2011, 08:53 PM

Thank you so much, you have saved me hours and hours of time. I think JBeaucaire works best for me. However, could you please help me understand the (INDIRECT("'" & $B2 & "'!$H11")<>""

**JBeaucaire** · 03-17-2011, 12:53 AM

Click on the cell and use the Evaluate Formula icon on the Formula Auditing toolbar. It will let you "step" through the formula one calc at a time and you can see how it works.

Press F1 in Excel and read up on INDIRECT() and how it lets you piece together text strings to create a cell/range reference, then use that reference in a formula.

======
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.