Customer Sheets and Customer List In Same Spreadsheet

Topic

New Reply

I’m new to Excel 2007 so I need the 2nd grade explanation

I have 20 customers.

This is what I have:

I have a sheet for each customer with contact and demographic information in a vertical format, 1 customer per sheet, sheet name will be customers last name, a-z

This is what I want to do:

On the 21 sheet I want a detail list various fields, name address, telephone, email, etc from all 20 customers in a horizontal format, 1 customer per line, all customers per last detail list sheet

When changes are made to the individual customer sheets they are reflected in the last detail list sheet

When changes are made to the detail list sheet they are reflected in the individual customer sheets

I need to be able to insert the 21st, 22nd customer alphabetically and have everything move to the right

Can someone provide me with a sample .xls spreadsheet or point me to tutorial that that demonstrates the above requirements?

Thanks, John

PS: What is the correct terminology for what I’m trying to accomplish?

Reply | Quote

Replies

When changes are made to the individual customer sheets they are reflected in the last detail list sheet

When changes are made to the detail list sheet they are reflected in the individual customer sheets

PS: What is the correct terminology for what I’m trying to accomplish?

John,

The technical term would be IMPOSSIBLE! 😆 At least w/o considerable VBA programming. If you only wanted to update in one direction, e.g. Detail to Summary that could be done with formulas. Also you need to put your summary sheet as sheet No. 1 (far left) then you don’t have to worry about it moving when you add a new sheet (again this could be overcome with VBA programming).

Some sample data would be very useful in further guidance. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

This is not my design/approach I inherited this from my club secretary

The real file has about 200 records

one direction, e.g. Detail to Summary that could be done with formulas would be great

.

Reply | Quote

John,

Before we proceed I have to ask..do you want to fix this thing?
This is really a Membership Database and as such should be done in Access (or other database program) preferably, or at least if you are using Excel why even bother with the detail sheets? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I understand it is an abortion

Can this be imported into a new Excel sheet, so I can have one row/record per member so he can stille work on it in a list format and I will work on bringing the excel file into a Access mdb?

Not to worry about the field descriptions

Reply | Quote

John,

Yes, it can be done with a little VBA. Give me some time and I’ll get on it. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

John,

Ok, here’s my solution.

The attached workbook contains the macro and the summary sheet necessary to get this done.
You need to copy the summary sheet to your a COPY of your production workbook (always work with a copy).
Then you need to copy the VBA to your workbook copy. I often find the easiest way to do this is to Export the module from my workbook using the VBE then close my workbook and open your copy and import it. It can be done with both workbooks open but it’s easy to get confused with two workbooks open. You don’t need to worry about the data already in the Summary workbook as the code will overwrite it since you’ll have more data that the test workbook but you can delete the data if you wish but DON’T delete row 1!

After you run the macro on your copy you can then delete all the detail sheets if you want just the Summary in that workbook. You can also delete the Macro (VBA Code) and resave it as a .xlsx type if desired.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Wow, thanks for this, worked great, just had to change the following:

Cells(lCurRow, 3).Value = shtCur.[K3].Value ‘FirstName

I like the way you parsed the csz, normalizing to lowest level

After further review and working with a secretary who doesn’t want to give up Excel

How would I create a form similar to the member tab, so when I click a member in the Summary page the form would open with
that members information displayed?

I have the form button in the tool bar

When I click on a member Agnello and form button the form displays the first member Adams

What kind of look-up vba code can I use to click on member Agnello and have member Agnello display in the form?

I goggled Excel form with row look-up without success

Reply | Quote

John,

You just need a little code in the form_open event. If you can post another cut down version of your workbook I’ll be glad to take a look. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Can you get sample excel spreadshhet from your solution above?

Attached Files 2013-09-10 11:07

File Type: xlsm support4john LYC_Member_Data_Summary-1_MS_20130909_Test.xlsm (33.9 KB, 2 views)

Reply | Quote

John,

Try again the file didn’t get uploaded. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Here you go

Reply | Quote

John,

That’s the file I sent you. I need the one you’ve added the form to. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi

As a matter of interest, in the file in post#12:
1. switch to the sheet [Summary]
2. put the cellpointer anywhere within the data records, e.g. click on cell [A1]
3. then, in the top-panel ribbon, select Data then, third item in list, Form

This will show the data records in ‘Form view’.
With this view, you can click the [Criteria] button, which will show an ’empty Form’, and you can then enter a search value (or combination of values etc) and then click the [Find Next] button etc etc.

Just thought you might like to know that this Form view is there already.

zeddy

Reply | Quote

Thanks zeddy, that was the form I was talking about initially

RG, Sorry, I was clicking the Form (Standard/Default) button zeddy is refrencing which list all the fields, do I understand correctly that this Form can’t be coded to opened to a specific row? It only does what is available when the form is opened?

If that is the case, looks like I’ll have to create a custom User Form

If I create a custom user form, is there Excel a form wizard (like Access) that will create all the field names and labels on the form automatically?

Reply | Quote

Hi

The built-in Excel Form cannot be customized.
But you could have a look at the free one that John Walkenbach has written:
http://www.j-walk.com/ss/dataform/

zeddy

Reply | Quote

John,

Sorry, no Wizard. You’ll have to build the custom form yourself in the VBE (Alt+F11) environment. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks zeddy, going to use j-walk til I get time to do custom form

Thanks RG for your Summary code, are there any sample Excel VBA databases/websites/tutorial you can recommend that demonstrate database and form techniques?

When I started with Access there was Rogers Access Library, Allen Brown & FMS that have sample database’s to demonstrate the capabilities of Access, are there sites like that that demonstrate the capabilities of Excel?

Reply | Quote