In the attached workbook, on the Final Report tab, I'm trying to replace all the #REF! portions of the formulas with the value that is in column B. So C40, would replace the #REF! with TCV!. I'm sure an indirect formula would work, but I don't know how to write it. Any help is appreciated.
Last edited by narrowgate88; 04-02-2013 at 04:18 PM.
why not just recreate the formulas?
you cannot look inside a formula anyway, all excel sees is the answer, not the formula
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Each month I'll be deleting the existing tabs and replacing them with the next month's info. I want to be able to copy in the entire tab rather than copying and pasting from one tab to another.
Sometimes you need to choose between
what is easier
what is more efficient
Using lots of indirect formulas will "significantly" decrease the performance of your book.
It would be MUCH more efficient instead of deleting and recreating the tabs,
To just copy paste the contents from the new tab into the old one.
And all the formulas will still work as always.
You may be right. I'd at least like to test the theory by trying the indirect formula, unless another formula would work better.
Well to impliment indirect, we need to see a WORKING formula with normally entered sheet references.
And you describe which part of that formula should be replaced by a value in which cell.
If thats what you are doing, I would suggest not deleting the "old month", you can then enter the month (tab) name on the summary sheet, and we can show you how to set up an indirect() for that. Keeping old months means you can always go back and check how things were months ago
assuming your data is in A1:20, and you enter a month (tab) name in N41, yu could use something like...
=SUMIF(INDIRECT(N40&"!A1:A20")," Private",INDIRECT(N40&"!B1:B20"))
Last edited by FDibbins; 04-02-2013 at 04:08 PM.
@FDibbins - well that would mean adding 16 tabs per month. I'm afraid that would get too large too quickly. Each month, after the report is generated, it's saved to PDF, for historical purposes.
@Jonmo1 - The formula for C40 s/b this before the indirect is added.In this cell, I want to replace the
Please Login or Register to view this content.with the new reference.Please Login or Register to view this content.
did you take a look at my last edit?
If B40 = TCV
Then
=SUMIF(TCV!$A:$A," Private",TCV!$G:$G)
becomes
=SUMIF(INDIRECT("'"&B40&"'!$A:$A")," Private",INDIRECT("'"&B40&"'!$G:$G"))
@FDibbons - sorry, yes, I'd missed your last post.
Thanks to both of you. I was trying to put the Indirect first, but now I see that the whole indirect formula replaces the tab reference. That is what I was doing wrong. This works now. Thank you for your time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks