Inexperienced -- please help

[hotcat]

I do not know how to write any code and have limited Excel skills.

I have a financial spreadsheet (2 pages). 1st page is a Summary sheet showing 3 months of financial information (this is what I want to automate). The 2nd page is Detailed, and shows Jan-Dec values which are manually input. I need to be able to find a script that I can use on the summary page to automatically look up the data, and then roll over to the next months.

i.e.
If the detailed worksheet is for the month of "August" then I need the Summary sheet to show the cells from the Detailed worksheet which contain the financial data for June, July and August.

When next month end occurs the Summary sheet should then show the values for July, August and September. I'm not certain if I can have the headings "July", "August" "September" also automated.

The data is set up in rows.

I was thinking:

If Detailed sheet, Cell Z18 (last cell for December) = Empty

Then Go To Z17

Else (I don't know how to phrase this)

Detailed sheet, Cell Z18 should put that value into Summary sheets, cell D7

Any help would be appreciated. I am out of my element here.

Thanks.
This message was edited by hotcat on 2002-09-29 05:54
This message was edited by hotcat on 2002-09-29 05:56

 

[bolo]

Could you give us a bit more information about the sort of information u need in the summary sheet. Perhaps you can provide us with a small example?

 

[plettieri]

The html worksheet utility by colo available below is quite helpful in showing others what you would like to see.

pll

 

[bolo]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	june		Detailedsheet
2
3
4	january	february	march	april	may	june	july	august	september	october	november	december
5	2	4	5	3	6	6
6	3	4	6	3	3	3
7	1	2	2	2	6	3
8
9	6	10	13	8	15	12	0	0	0	0	0	0
10
11
12
13
14						SummarySheet
15						april	may	june
16						8	15	12
17
18
19
Sheet2

hi i am not sure if this is wot u want. i put the info on one sheet to make it easy to view. Wot happens here is that when infor is typed in the the next month everything is automatically updated and the summary moves to the next nmonth. To do this u need to place the following code into the detailed sheet.

Public Sub Worksheet_change(ByVal Target As Range)
If Not Intersect(Target.Cells(1, 1), Sheet2.Range("details")) Is Nothing Then
Sheet2.Range("h15").Value = Sheet2.Range("A1")
Sheet2.Range("h15").AutoFill Destination:=Sheet2.Range("f15:h15"), Type:=xlFillDefault
Sheet2.Range("f16").Value = "=hlookup(f15,Sheet2!$a$4:$L$9,6,false)"
Sheet2.Range("f16").AutoFill Destination:=Sheet2.Range("f16:h16"), Type:=xlFillDefault
End If
End Sub
You also need to set up a few names.
name the month row "month" - i.e. name A4:l4 month.
also create a name called details. In the formula put in:

=OFFSET(Sheet2!$A$5,0,0,3,COUNTA(Sheet2!$5:$5)).

i hope all this makes sense. let us know how you get on. If u have problems give us ur email and i'll send u a spreadsheet with it all on. Hope it is not too confusing.

 

[hotcat]

I wasn't able to use the Coolo software on XP. Below is my attempt at an example:

There are 4 divisions that each have a separate worksheet. Each division reports on the same items for each month. Data is manually input.

i.e. Column G = June

Worksheet name = Vancouver Division

For the months Jan-Dec there will be a value that is manually input for each category. Columns Headings (Jan-Dec) are B-M (data in rows).

Column A headings: Col. G
June
Financial Results
- Adjusted Revenue 112
- Adjusted Field Service Revenue 87
- Backlog 12

The SUMMARY sheet is a compilation of data for each division (reads from 4 worksheets in the same workbook).

Financial Results
- Vancouver
- Edmonton
- Montreal
- Toronto

Main headings (merged across 3 cells) are:
- Adjusted Revenue
- Adjusted Field Service Revenue
- Backlog

Below each main heading are 3 months reporting. i.e., April, May, June

What I want to accomplish is to have a macro or function go to the last cell (June) in which I have input data and enter that value on the Summary page. If the last cell G18 has a number in it (June), then it should show the value of G18 and the previous 2 cells (E18 for April, F18 for May).

I thought I would have to pick an end point and then go forward. If I started at December and that cell was empty, then I could have the function move forward until it found a value (start point) and then insert that value + enter data for the previous 2 cells as well.

Does this make sense?

 

[hotcat]

Hi, Bolo.

Thank you for responding to my message. I'm am definitely in over my head. I just don't seem to get these formulas/macros. I have set up the start of my spreadsheet and would be happy to send it to you if you would look at it for me. Email address: gvanempel@cogeco.ca.

I'm an Executive Assistant and have reasonably decent computer skills, but I hae not previously dived into this kind of function. Most of my experience has been with computer graphics and word processing software. I really, really appreciate any assistance you can provide.

Thanks a million!

C