Inexperienced -- please help

hotcat

New Member
Joined
Sep 28, 2002
Messages
4
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
The html worksheet utility by colo available below is quite helpful in showing others what you would like to see.

pll
 

bolo

Active Member
Joined
Mar 23, 2002
Messages
423
Book1
ABCDEFGHIJKL
1juneDetailedsheet
2
3
4januaryfebruarymarchaprilmayjunejulyaugustseptemberoctobernovemberdecember
5245366
6346333
7122263
8
96101381512000000
10
11
12
13
14SummarySheet
15aprilmayjune
1681512
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

New Member
Joined
Sep 28, 2002
Messages
4
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

New Member
Joined
Sep 28, 2002
Messages
4
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
 

Forum statistics

Threads
1,144,326
Messages
5,723,724
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top