AboutTom Ogilvy Expertise Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
I am Dhandapani from Bangalore. I have around 100 offices all over India. Every month I am receiving the expenditure data from the 100 units for consolidation. I am entering each office data in different worksheets. The Monthly data of one unit I am entering like this.
A B C D E F G
April May Jun Jul Aug
1 PAY 500 100 50 300 200
2 D.A. 200 150 60 120 700
3 House Rent 120 450 90 900 800
4 CityAllowance 400 900 80 700 300
Now I have created one sheet "Consolidation". This sheet contains the contents in column "A" and Month Column in "B"
Now if I select the month name the values in the corresponding month data should be updated in Consolidation worksheet. eg. If I selection "May" The data in the May month should be updated in consolidation.
Kindly answer this question by using VBA code Sir.
Dhandapani
Bangalore.
Answer You show "PAY" as being in row 1, but I assume it is in row 2 (actually cell A2) and the word "April" is in B1
I will guess that you want the labels in column A and the monthly data copied to the next blank row in consolidation
sub CopyData()
set r = ActiveCell
set d = worksheets("Consolidation").Cells( _
rows.count,1).End(xlup)(2)
set r1 = r.parent.Range(r.offset(1,0), _
r.parent.Cells(rows.count,r.column).end(xlup))
r1.copy d.offset(0,1)
set r1 = Intersect(columns(1),r1.EntireRow)
r1.copy d
End sub
If you want it to find the month in row 1 of consolidation, then where is the data to be placed. Is it to be added to the existing data in sheet consolidation? I will assume that and do a paste special ADD
sub AddData()
set r = ActiveCell
set d = worksheets("Consolidation").rows(1).find(r.value)
set r1 = r.parent.Range(r.offset(1,0), _
r.parent.Cells(rows.count,r.column).end(xlup))
r1.copy
d.offset(1,0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlAdd
End sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
