LinkBack URL
About LinkBacks
Hi all,
I have the following formula and it won't work any more.
=SUMIFS(INDIRECT("'"&$F$4&"'!G:G"),INDIRECT("'"&$F$4&"'!C:C"),$E13,INDIRECT("'"&$F$4&"'!A:A"),$E$4)
all I did was change the names of the tabs
Can anyone help
Thanks
loopiloo
Hi all,
I have the following formula and it won't work any more.
=SUMIFS(INDIRECT("'"&$F$4&"'!G:G"),INDIRECT("'"&$F$4&"'!C:C"),$E13,INDIRECT("'"&$F$4&"'!A:A"),$E$4)
all I did was change the names of the tabs
Can anyone help
Thanks
loopiloo
sorry to say I unable to get your query, could u plz explain what exactly u want in output
Thanks - Naveed
-----------------------------
If the suggestion helps you, then Click * to Add Reputation
To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
In using the Indirect function, you are referencing the value/string in cell $F$4 (in your example) of the current tab/worksheet. If the value in cell F4 is not the name of a tab/worksheet in your workbook, you will receive an error.
Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.
Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
If you're happy with an answer given, please click the * under the person's name to boost their reputation.
What does "won't work any more" mean?
Do you get an error? An incorrect result? Something else?
If you changed the sheet (tab) name did you also make the appropriate change to cell F4?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for reply's and sorry for delay in getting back to you...
I really haven't changed the names of the tabs I simply moved them around
ie names of tabs before change D11, D12, D13, D14, D15
names of tabs after change D12, D13, D14, D15, D16
Got me stumped why this should stop the formula from working?
Any help would be great
Thanks
loopiloo
Thanks for reply's and sorry for delay in getting back to you...
I really haven't changed the names of the tabs I simply moved them around
ie names of tabs before change D11, D12, D13, D14, D15
names of tabs after change D12, D13, D14, D15, D16
Got me stumped why this should stop the formula from working?
Any help would be great
Thanks
loopiloo
PS: i change F4 value relating to the sheet i want the formula to take data
Last edited by loopiloo; 05-27-2015 at 04:54 PM.
You still haven't explained what that means.Originally Posted by loopiloo
You may need to post the file (or a sample file) that demonstrates the problem you're having.
No it does not show any error code, it just shows 0, as though there is no data to collect.
The other thing is that if I change the tab names back to what they were originally it works just fine
Changing the order/sequence of the sheets, should not affect that formula, please upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
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
Attached is the example file...
I change the number in E4 which will give the correct reference matching the tab name (you will see when you open the example)
I tried changing the tab name from D12 to D13 in the attached file and again the same problem of it not displaying any data
Hope you can help me
The problem is due to cell F4 not automatically updating when you change the sheet name.
If you change cell E4 then cell F4 updates with the correct sheet name.
What you could do is on the D(whatever) sheets enter a formula that returns the sheet name then refer to that cell in your formula.
For example, this formula entered on sheet D12 in cell Z1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
Then, on sheet DYear enter this formula in F4:
='D12'!Z1
Then your other formulas will work.
Ok seems to work fine with that... thanks for your help...
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks