Advanced Basic Excel Project

kojak43 · Mar 27th, 2002, 08:44 PM

I would like to have a worksheet to hold data. The fields would run from A:Q.
Once data is entered in the initial worksheet, I would like certain fields from that data worksheet, to populate a second and a third worksheet. Those next two sheets would be formatted to produce a report and a pivot table.

I can do much of the formatting for sheets 2 and 3, but do not have the ability to write the necessary code to fill the 2nd and 3rd sheets.

The field headers in sheets 2/3 would mirror header in sheet 1.

I tried to search previous posts to get some direction for this, but could not figure out a successful term to ge the search to work.

I am reasonably confident this can be done, I'm just not experienced enough to do this.

Any help is appreciated.
K

Yogi Anand · Mar 27th, 2002, 08:49 PM

hi kojak43:
Let us say you have an entry kojak in cell A1 of Worksheet Sheet1
Now if you want to have the same entry in cell A1 of Worksheet Sheet2
Then in cell a1 of Sheet2 you will enter

=Sheet1!A1

I hope I have understood you right and not trivialized you request.

Please post back if this is what you intended ... otherwise explain a little further and let us take it from there!

Dave Patton · Mar 27th, 2002, 11:56 PM

A few questions:

- must you have sub sheets?
* do advantages outweigh the disadvantages?

- is it necessary to replicate the data?

- have your considered scenarios, hiding columns, pivot tables, data filters ...?
It may be easier to prepare the following from one main sheet of information
* prepare summary reports
* prepare custom reports

Dave Hawley · Mar 28th, 2002, 12:07 AM

Hi

On the sheet you wish to enter your data, right click on the sheet name tab, select "view Code" and paste in this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:Q2000")) Is Nothing Then
Sheets("Sheet2").Range(Target.Address) = Target
Sheets("Sheet3").Range(Target.Address) = Target
End If
End Sub

From now on whenever you enter any data on this sheet it within the range "A2:Q1000" it will be automatically mirrored on sheet2 and 3

kojak43 · Mar 28th, 2002, 08:21 AM

Thanks for your answers:
Yogi Anand:
I assumed I would have to something reasonably work intensive to accomplish what I wanted. And your response was what I thought I would have to use.
However, both Dave's answers might provide a different way.
Dave Patton:
I am not sure I can answer your questions as I do not know what Summary Reports or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no joy.
If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind leading the halt around here.)
Dave Hawley:
The reason I wanted to use different worksheets was I assumed I could format the subsequent worksheets into the two reports I wanted.
Your code would work if I needed to use contiguous cells. Worksheet 2 might use A,B,C,F,H,& Q. Worksheet 3 might use A,B,C,D,E,G,I,K,J,L & M
That jumping around cells leaves me, and my Excel talent, out in the cold.

DorenM · Mar 28th, 2002, 04:13 PM

Hello!

Was just wondering if the info. on sheets 2 and 3 is a filtered sub-set of the first sheet? If so, record a macro to filter the first sheet based on whatever criteria you need, and then copy and paste the filtered records to the other two sheets. Or create a macro that uses Advanced Filter to filter the records and then copy them to the other sheets as needed.

HTH's or gets you started in the right direction!
D.

kojak43 · Apr 1st, 2002, 02:15 PM

Dave Patton:
I am not sure I can answer your questions as I do not know what Summary or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no help.
If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind (me) leading the halt (her) around here.)

Mar 27th, 2002, 08:49 PM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	hi kojak43: Let us say you have an entry kojak in cell A1 of Worksheet Sheet1 Now if you want to have the same entry in cell A1 of Worksheet Sheet2 Then in cell a1 of Sheet2 you will enter =Sheet1!A1 I hope I have understood you right and not trivialized you request. Please post back if this is what you intended ... otherwise explain a little further and let us take it from there! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 08:44 PM	#1
kojak43 Board Regular Join Date: Feb 2002 Posts: 270	I would like to have a worksheet to hold data. The fields would run from A:Q. Once data is entered in the initial worksheet, I would like certain fields from that data worksheet, to populate a second and a third worksheet. Those next two sheets would be formatted to produce a report and a pivot table. I can do much of the formatting for sheets 2 and 3, but do not have the ability to write the necessary code to fill the 2nd and 3rd sheets. The field headers in sheets 2/3 would mirror header in sheet 1. I tried to search previous posts to get some direction for this, but could not figure out a successful term to ge the search to work. I am reasonably confident this can be done, I'm just not experienced enough to do this. Any help is appreciated. K

Mar 27th, 2002, 11:56 PM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	A few questions: - must you have sub sheets? * do advantages outweigh the disadvantages? - is it necessary to replicate the data? - have your considered scenarios, hiding columns, pivot tables, data filters ...? It may be easier to prepare the following from one main sheet of information * prepare summary reports * prepare custom reports

Mar 28th, 2002, 12:07 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi On the sheet you wish to enter your data, right click on the sheet name tab, select "view Code" and paste in this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:Q2000")) Is Nothing Then Sheets("Sheet2").Range(Target.Address) = Target Sheets("Sheet3").Range(Target.Address) = Target End If End Sub From now on whenever you enter any data on this sheet it within the range "A2:Q1000" it will be automatically mirrored on sheet2 and 3

Mar 28th, 2002, 08:21 AM	#5
kojak43 Board Regular Join Date: Feb 2002 Posts: 270	Thanks for your answers: Yogi Anand: I assumed I would have to something reasonably work intensive to accomplish what I wanted. And your response was what I thought I would have to use. However, both Dave's answers might provide a different way. Dave Patton: I am not sure I can answer your questions as I do not know what Summary Reports or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no joy. If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind leading the halt around here.) Dave Hawley: The reason I wanted to use different worksheets was I assumed I could format the subsequent worksheets into the two reports I wanted. Your code would work if I needed to use contiguous cells. Worksheet 2 might use A,B,C,F,H,& Q. Worksheet 3 might use A,B,C,D,E,G,I,K,J,L & M That jumping around cells leaves me, and my Excel talent, out in the cold.

Mar 28th, 2002, 04:13 PM	#6
DorenM New Member Join Date: Mar 2002 Posts: 3	Hello! Was just wondering if the info. on sheets 2 and 3 is a filtered sub-set of the first sheet? If so, record a macro to filter the first sheet based on whatever criteria you need, and then copy and paste the filtered records to the other two sheets. Or create a macro that uses Advanced Filter to filter the records and then copy them to the other sheets as needed. HTH's or gets you started in the right direction! D.

Apr 1st, 2002, 02:15 PM	#7
kojak43 Board Regular Join Date: Feb 2002 Posts: 270	Dave Patton: I am not sure I can answer your questions as I do not know what Summary or Custom Reports are. They could be the answer. I tried to find out what they were in a search of MrExcel - no joy. I even went as far as M.S. Excel (shutter) Help. - no help. If I can learn to create the reports, and then just turn it over to the user that inputs data, and have her press a key and the report appears,that would work. However, if she has to do anything more, it will not work. (it is sort of like the blind (me) leading the halt (her) around here.)