Add formula based on a fixed number

[Guest]

Dear All

I have a sheet where i am trying to set up for expenses
In first column i have the description and then there are 12 columns, one
for each month
In another sheet i am trying to make a consolidation of expenses.
I have in cell A1 the month
There is any possibility to bring in column B the sum of expenses from the
other sheet based on the number of the month?
For example if the month is September (9) to add the column b to j
if month goes to October (10) then to add column from b to k
Any ideas ?

 

[Guest]

Hi,

If I understand your posting correctly, in a sheet (say Sheet 1) the Row 1
contains "Description" in A1, and month names in B1....M1, and there are
several rows below, containing names for expense categories in Col A (i.e.,
A2, A3, .....) and amounts in the corresponding rows in Col B, Col C,.....Col
M for each row. And in another sheet (say Sheet 2) you want to enter a
month's name (say "Sep") in A1, and want the formulas to return the total
from Jan to that month (e.g., Jan-to-Sep) for each expense category.

Try the following formulas in Sheet 2:
In A2, =Sheet1!A2
In B2, =SUM(Sheet1!$B2:OFFSET(Sheet1!$B2,0,MATCH($A$1,Sheet1!$B$1:$M$1,0)-1))
Drag the formulas down the rows 3 ..... last row.
It is important that the month name you enter in A1 of Sheet 2 should be
identical to one of the month names in B1...M1 in sheet 1 (i.e., you can not
have 'September' in Sheet 1 and enter "Sep' in Sheet 2).

Regards,
B. R. Ramachandran

 

[Guest]

Thank you Ramachandran


Ο χÏήστης "B. R.Ramachandran" έγγÏαψε: