Sorry to bring up old threads but this is almost exactly what i was looking for. Except i have one variation. Instead of referencing a list of cells that refrence my spreadsheet names. Can i use a wild card for the refrence? for example...
All of my tabs start with "WE" and then end with the date of the week. So i am trying to refrence each tab using this formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&WE?&"'!A:A"),C8,INDIRECT("'"&WE?&"'!D:D")))
So between the &'s i want to reference any spreadsheet with the text string "WE". How would i do that?
I don't think that's possible formulawise. We need (a) list of possible sheets or (b) a procedure that generates them.
Example for (b)...
"Sheet"&ROW(INDIRECT("1:"&X2))
where X2 = 7.
hmm. I kind of get what you are saying but dont understand your example. If i were to generate a list of sheet names starting from A2 and going to A300 using the code that is on page 1 of this thread. Would i be able to auto update the list as i make new tabs just by running the code again?
Sub ListSheetz()
Dim i%
For i = 1 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Next i
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-12
Range("B9").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B8").Select
End Sub
excellent... Now i cant seem to get the code to work. I created a module (i think) and when i enter the forumla as specified and pres CTRL+ Shift + Enter i get #NAME? as the result.
ahh google is a great friend. I figured it out with a macro.
I had to modify it so that i got the resutls in the cells i wanted. Perhaps it can be cleaned up a bit, but it works.
[...]
Thanks for you help though!
Hi, Aladin or other Excel expert,
I'm rather new to Excel. How do you set up the range '&SheetList&', mentioned in method 1? BTW, why are 2 levels of quotes required inside the INDIRECT parens?
Date | | Credit Crd: Business | | Dr | Cr | | | | Expenses | Income | |
17/07/2013 | -15.95 | O'REILLY MEDIA 0104 SEBASTOPOL 13.99 USD 0.46 AUD | -$15.95 | CAF | Helpdesk | QHSE | PD, Subscriptions, Resources | | |||
| | | | | | | | | | ||
|
I have posted in another thread [3527876], but hope for help here as well...
Thanks for this one, I was using SUMIF's all over the place and the workbook was acting like it had a case of the flu!
In my case, I'm playing around with this to troll income and expense categories, to see how far I can go with a simple profit and loss system. I'm using ranges to get Excel to allow nested Validated Lists for tagging expense and income items and it's all fine and dandy, even the tax exclusions and component reporting.
I'm being lazy, and assuming all transactions are a tax input or a tax output ..unless tagged otherwise, so that I only have to record the exceptions, hence the divide by 11 to get the 10% tax component out.
The sheets' data areas are like this:
Date Credit Crd: Business Dr Cr Expenses Income 17/07/2013 -15.95 O'REILLY MEDIA 0104 SEBASTOPOL 13.99 USD 0.46 AUD -$15.95 CAF Helpdesk QHSE PD, Subscriptions, Resources
<tbody>
</tbody>
But....
I have two cost centres running off of the same transactions, and I'm wondering how to slip in a condition to the formula to give me the CAF paid on purchase (an accounting category of GST, items over $1,000):
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",INDIRECT("'"&SheetList&"'!f2:f2000")))
/11
THe formula works fine as it is, but covers both cost centres,
...I'd like to interrogate column I for a particular cost centre "Helpdesk", and only return CAF when "Helpdesk" is in column I on those CAF hits.
sooooooo .... any geniuses out there?
And yes, I can get around it by either:
- having separate tax columns for each cost centre; or
- prefixing the tax code with a cost-centre code
..both are messy in their own way, and I'm trying to get this to be user-friendly; both usage and visually.
=SUMPRODUCT(
SUMIFS(
INDIRECT("'"&SheetList&"'!h2:h2000"),"CAF",
INDIRECT("'"&SheetList&"'!i2:i2000"),"Helpdesk",
INDIRECT("'"&SheetList&"'!f2:f2000")))