Creating automatic template in excel

[Benplayford]

Can anyone help me?

I want to create a template in excel, much the same way as a letter template can exist in word. What I mean is that once the file is selected you then have to input certain information fields which are then automatically entered into the correct areas of the workbook, thereby eradicating any duplication of entering the same in formation in two or more different places.

My VBA knowledge is limited so I was wondering if there was a quick way of doing this or whether someone could point me in the direction of how to start the process.

Responses would be welcome,
Thanks

 

[Dreamboat]

Create a spreadsheet with all of the required links.

In other words, if you want them to enter their data in cell A2 on sheet1 and have it appear in the same place on sheets 2 and 3, then you can put the following formula in cell a2 of sheets 2 and 3:

=if(isblank(sheet1!a2),"",sheet1!a2)

That way, you won't get errors in those cells when a2 on sheet1 is blank.

If you can send me a sample layout, I can show you better than tell you.

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[DaleWatson123321]

Hi Ben,

To get you started, I suggest you have a look at (and practice with) "Data - Validation" - i.e. click on "Data" on the main menu, and choose "Validation".

Then, under "Allow", choose "List".

Then, for your list location - i.e. the location where you will enter a list from which the user will be able to make a selection, place this list on a separate sheet so it will be "out of reach" of the user.

Once you enter data in your list, you'll notice that when you place your cursor on the cell where you have entered the "Data Validation" function, there will be a small button with a "down arrow". When you (or the user) clicks this down-arrow, they will be presented with the list of choices you entered on the separate worksheet.

You'll also notice that you can set up "Input Messages" and an "Error Alert".

Using this method (Data Validation), you can set up your own customized "input screen" - where you would add headings and other instructions regarding the input-data for the user to enter.

In turn, this data should be referenced by formulas which you should enter on a separate worksheet. On the separate worksheet, you can mix and match the data however you need to. To write a simple formula, simply type the equals sign ( = ) and hit <Control> <PageUP> to go to the previous sheet. Then move to the cell which you want to reference, and then hit <Enter>. Or, you can also click on the sheet &quot;tab&quot; (e.g. &quot;Sheet1&quot after typing the &quot;equals&quot; character.

Without having the specifics of your task, it is more of a challenge to appreciate what is preferable for your particular sitation.

I hope this at least &quot;gets the ball rolling&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca