Can excel be used to mirror a data table on a seperate page based on category

[ahaase]

I've signed on board for a project that seems to be over my head. I'm trying to create a form similar to the word document I've attached. I would like Sheet 1 to look as it does in this word document, and the other various sheets to autofill based on the information entered under chain of title. For example, if the type of document entered (or selected from a drop down menu) is Mortgage, I would like to be able to have that particular table of info describing that document fill onto, say, page 3 titled "Encumbrances" while remaining on the first page in chronological order with all other documents.

Did that make sense? I've researched like crazy, but I'm not really sure what exactly I'm even looking for. I know that it would be really great if I could use a Userform to fill in the information listed, but so far I've only been able to figure out how to use userform to populate columned info, not tables.

Is Excel even the right app to do this with? If someone could point me in the right direction that would be wonderful! If my description needs more explaining just let me know and I'll give it another stab.

In advanced I'd like to say thank you all so much!

Alma

[David A Coop]

ahaase - User Form to Data Page.xlsm

Welcome to the forum ahaase,

This is quick and nasty, but I just want to show that it can be done. (I haven't got anything to transfer the data, but have included a proposed data sheet).

However, there is a lot of work involved, because you will need macros and a lot more information than you have provided. I know form experience that it will take a couple of days to get it right.

I think that Excel could be a suitable application, depending on volume. If you are trying to develope a sizeable database, however, you might need to look elsewhere. (I don't have any expertise there).

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[FDibbins]

Hi and welcome to the forum

If this is an excel question, it would be better to upload an excel workbook. instead of a word doc. If this is a Word question, I can move it to that forum for you?

[ahaase]

Wow! Thanks David, that is awesome! I've been working on this for hours and I haven't gotten nearly that far. I did figure out how to develop a userform which has the type of documents (not with the cool dropdown you made, i'll have to work on that), grantee, grantor, etc. fields, but I cannot figure out how to get it to fill columns instead of rows. Pretty much just a learning night for me as I've never used the VBA feature of Excel and have only mildly toyed with it in Word. I've attached what I did (don't laugh), its got a big picture header for p1 is why there's a big white chunk at the start. I've got to say, yours looks way better! Do you know of anyway to have a userform fill areas the way you got them laid out? Just a point in the right direction would be fantastic!

In the longrun i'd LOVE to use the format you've got, with a userform easy fill that creates and populates those tables, then depending on what is selected as type of document it would also fill whatever page corresponds to the category. So, the initial page would have all of the documents laid out chronologically, and then each page would have the different categories grouped together. so the chain may list:

1.Warranty Deed
2.Deed of Trust
3.Judgement
4.Quit Claim Deed
5.Warranty Deed
6.Warranty Deed

each with their own detail tables and inserted chronologically by the user, then page 2 would be for "Vesting Deeds" and would repeat the same detail tables for:
1.Warranty Deed
4.Quit Claim Deed
5.Warranty Deed
6.Warranty Deed

Page three would be titled "Encumberances" and would provide the detailed tables for the:
2.Deed of Trust
3.Judgement

and so on. These would not be used to create a large database, it would probably be limited to 4 pages, with max 14 items in the chain.

Hope that paints a better picture. I REALLY appreciate your help, I didn't expect such a hands on response at all!

[ahaase]

Thank you very much for the welcome FDibbins! The Word file was attached because it is a very simplistic version of the document I am trying to create in excel. I didn't/don't think Word is capable of the bells and whistles I want to add though, it looks like Excel is going to be my app Thank you for the offer though!

Have an great night,

[FDibbins]

OK thanks for the update

[David A Coop]

ahaase - User Form to Data Page 2.xlsm

Hi Alma,

Here is a quick mock up of what I would do.

I don't understand what you are trying to achieve with the various pages etc, but this simple example shows you how you can easily take the data from your Form and move it to another sheet. (I have called it Data).

I have changed the approach to the input form. All the data is input DOWN the sheet. It looks like the top 8 (or 10) rows are constant, while the bottom 10 (or 12) rows vary. Instead of having several input blocks, I have provided just one.

When you click POST, all the data from the form is written to the NEXT row on the Data sheet. Then the variable part is cleared so that you can enter another document. The Clear Form button does what it says!

I have made the form this way for ease of entry. Most people set Excel up so that Enter takes then to the cell below, so it is much easier to key the information.

In addition, it is very simple code - just one line - to get the data across to the other sheet.

I have included one drop down in B12 - you may want more! If you don't know how, I recommend you Google Chandoo (from India) or Contextures (from Canada). Both Chandoo and Debra are excellent at explaining what to do.

Good luck with this.

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[ahaase]

Wow David, this looks fantastic! I'm scrapping what I've tried to come up with and going with your base. Thank you so so much, this is a huge step in the right direction. I think you probably saved me 2 weeks of frustration!

Thank you thank you thank you, I hope you're having a fantastic weekend!

[David A Coop]

You,re welcome, Alma, and thank you for the enthusiastic feedback!

Please remember to mark the thread as SOLVED when you're ready, and to click on the Add Reputation stars for posts which have helped.

David

[David A Coop]

You,re welcome, Alma, and thank you for the enthusiastic feedback!

Please remember to mark the thread as SOLVED when you're ready, and to click on the Add Reputation stars for posts which have helped.

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".