making one excel spreadsheet out of several doc and pdf forms

[lauraexcel]

I need to make one spreadsheet using 8-10 forms currently available in doc and pdf formats. I want to populate all the forms in the spreadsheet by filling in item once in top form--things like name. dob, ssn, etc. There are little or no calculations needed. My problem is creating an excel form that looks like the original, with spacing and graphics. Is there a way to import text and graphics in pieces to fill a block of cells? That need not be editable, but adjoining cells would have to be populated from top form on spreadsheet.

Thanks.

 

[Cindy Ellis]

Hello Laura, Welcome to the forum!

I don't have a complete answer to your question, because I'm not sure exactly what you have (your inputs) and what you want to achieve (your desired output).
I've read your question several times, and I think maybe you want each of the existing forms to be re-created within the worksheet (say on sheets 2 through 11), and have a "master" data input sheet that populates all the underlying forms so that data entry only has to be done once.
That is definitely possible, but it will take some work...a bit more work in the case of the pdfs, and (probably) not too much in the case of the Word docs.

If I'm on the right track, post back and I'll help you with the steps to set this up.

 

[lauraexcel]

Yes, you figured out what I meant to say So far, I have converted some pdf's into jpg's, and used the picture as a header (the only way I could find that the picture will print). I put the cells over where I want the data to go. This works. I have to use a separate sheet for each page, but that's OK. There is probably a more elegant way to do it. My problem will be when I get to the really complicated pdf's with radio buttons and calculations. I don't suppose there is a more direct method of putting a pdf into the sheet. BTW, how is a doc easier? I am having a hard time because of the amount of narrative I'm trying to fit into a cell. If I merge cells, it wont let me change the contents. I don't know ahead of time how much narrative-type information that will be imported, so I could end up with a huge cell waiting for one sentence. Thanks for your interest.

 

[Cindy Ellis]

My problem will be when I get to the really complicated pdf's with radio buttons and calculations.

I would probably try to recreate the whole form in Excel rather than using the original as a background image (unless I've misunderstood). You can use the design toolbox to add radio buttons, and formulas for the calculations.
For the straightforward data entry fields, you can create a master sheet with a field for each necessary entry. In the sub-forms (all the rest of the sheets) you would just refer to the cell on the master sheet by including the sheet name with the reference. Excel will add in the sheet name for you if (for instance) you type an "=" in the cell on the sub-form, then click the "master sheet" tab, select the cell with the information you want transferred, then press Enter. You probably already knew that...but just in case

BTW, how is a doc easier?

My assumption is that you could simply copy a section of a word document as-is, then re-format the information in Excel as needed. I'm not sure why Excel won't let you change the contents of merged cells...I try to avoid merged cells as much as possible, but sometimes it makes sense to use them. I've never had a problem not being able to change the contents, though.

Regarding the amount of narrative...how much information needs to go into a single cell, and related to that, what version of Excel are you using? Newer versions of Excel (2007 and 2010) can handle much more information in a single cell. Is the problem that the cell needs to re-size to handle varying amounts of information that might be entered?

I don't feel like I'm being much help, but I'll do what I can to help you find the best way to accomplish your goal.

 

[lauraexcel]

I am using excel 2010, and it is new to me. I think you are right about just doing the form over from scratch. I just never did radio buttons before. The narrative problem is just cosmetic. It is not on anything I have to give to anyone, it is for my own use. Other forms get sent out. It would be helpful if I could break the narrative into sentences. I got a bit of code I found that separates names so you can change a name to last, first. that is based on finding the space. I would have to loop until I run out of sentences, and my programming is rusty. Having one sentence per cell is better because it can run over to adjoining cells; it just can't run over if there are a lot of sentences because it will mess up cells I am using to the right. Do you know of any code that will break down concatenated data into sentences? I know the opposite is easier. Thanks.