Solved Excel Problem (referring to cell contents in function)

Microsoft Office excel 2007 home & stude...
January 12, 2012 at 19:26:25
Specs: Windows 7
I have two worksheets, one with debits categorised by date in the first column, then the amount of the debit. The second worksheet is a total of credits minus the debits and I want to sort this by date. The first column is start date, the second is end date.

If I want a sum of the debits in my Total Worksheet I want to use the following formula:
=SUMIFS(Debits!B2:B8,Debits!A2:A8,">Total!A2",Debits!A2:A8,"<Total!B2")
Where Total!A2 and Total!B2 refer to dates in format 01/01/2012. Unfortunately the result is 0. It only works when I replace the reference Total!A2 with an actual figure (ie. 13/02/2012).

I know this will have something to do with Variable Range or INDIRECT but have had no hope so far. Any help?

Also will want A2 and B2 to change to A3 and B3 as it is dragged down the worksheet...


See More: Excel Problem (referring to cell contents in function)

Report •


#1
January 13, 2012 at 04:59:54
✔ Best Answer
I believe the problem is due to the fact that you have the criteria argument enclosed in quotes.

When Excel sees quotes, it assumes that everything inside the quotes is a text string. Therefore it is not picking up Total!A2 or Total!B2 as ranges.

Try this syntax...it should solve both of your issues.

">" & Total!A2
">" & Total!B2

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 13, 2012 at 18:28:57
Well that worked! Thanks DerbyDad. I tried it outside the qoutes but without the & symbol. Just one other quick question, for the part B2:B8 is there a simpler way to make it B2:Indefinite? Becasue i want to include in the search all the entries minus the heading (B1). I would assume B:B includes B1 as well...

Report •

#3
January 13, 2012 at 18:45:09
And also is there a way to have an increment of 9 cells as you drag down? For example I want cell B2 to refer to Sheet1!A9 and cell B3 to refer back to Sheet1!A18 etc so that I can drag the formula down and fill all the cells underneath with the incremental change? Thanks :)

Report •

Related Solutions

#4
January 13, 2012 at 21:10:10
re: is there a simpler way to make it B2:Indefinite?

You could define a Dynamic Named Range and use that in your formula.

If you define a Named Range that refers to this formula, the Range will be B2:B(LastRowWithData) assuming there are no blank cells within the Data:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))

However, is including B1 really a problem? Does including B1 cause the SUMIFS to return the wrong results?

re: is there a way to have an increment of 9 cells as you drag down?

If you put this in any cell it will refer to Sheet1!A9. As you drag it down, it will increment by 9 rows in each cell:

=INDIRECT("'Sheet1'!A"&((ROW(A1)-1)*9+9))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question