Multiple Copies of the Same Excel Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1673545%22%20slang%3D%22en-US%22%3EMultiple%20Copies%20of%20the%20Same%20Excel%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1673545%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20document%20with%204%20sheets.%20I'd%20like%20an%20address%20from%20a%20database%20(this%20data%20base%20can%20be%20turned%20into%20an%20excel%20sheet)%20to%20print%20on%20all%204%20sheet%2C%20in%20a%20specific%20location.%20On%20top%20of%20that%20I'd%20like%20to%20make%20multiple%20files%20in%20the%20same%20format%20but%20with%20different%20addresses.%20Similar%20to%20mail%20merge%20in%20Microsoft%20Word.%20How%20do%20I%20do%20that%20quickly.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1673545%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680562%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Copies%20of%20the%20Same%20Excel%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795858%22%20target%3D%22_blank%22%3E%40Suraqah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20a%20bit%20difficult%20to%20picture%20exactly%20what%20you're%20looking%20for%2C%20so%20what%20I'm%20going%20to%20suggest%20may%20miss%20the%20mark%20entirely.%20I'm%20also%20assuming%20this%20is%20a%20one-time%20affair%20(which%20is%20to%20say%2C%20I'm%20going%20to%20give%20you%20some%20steps%20that%20probably%20could%20be%20automated%20at%20some%20point%20via%20a%20VBA%20routine%3B%20but%20I'm%20not%20going%20to%20go%20that%20far).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK.%20Here's%20how%20I%20would%20approach%20what%20I%20think%20you're%20describing.%3C%2FP%3E%3COL%3E%3CLI%3ETake%20that%20database%20and%20make%20it%20an%20Excel%20table%20in%20the%20same%20workbook%20as%20your%20three%20four%20sheets.%20I'm%20going%20to%20assume%20that%20the%20database%20is%20set%20up%20(and%20therefore%20the%20Excel%20version%20will%20be%20too)%20with%20first%20name%2C%20last%20name%2C%20street%20address(es)%2C%20city%2C%20st%2C%20zip....all%20those%20as%20their%20own%20columns%20nicely%20laid%20out%20in%20the%20format%20of%20an%20Excel%20table%3C%2FLI%3E%3CLI%3EAdd%20a%20column%20with%20numbers%20from%201%20to%20X%2C%20whatever%20your%20maximum%20count%20is%3C%2FLI%3E%3CLI%3EIn%20the%20appropriate%20cells%20of%20the%20four%20spreadsheets%2C%20write%20VLOOKUP%20or%20XLOOKUP%20functions%20functions%20to%20retrieve%20the%20full%20address%20based%20on%20reference%20number%20off%20to%20the%20side%20in%20the%20first%20sheet.%3C%2FLI%3E%3CLI%3ERun%20through%20the%20numbers%2C%20printing%20the%20sheets%20or%20whatever%20you%20are%20planning%20to%20do%20with%20them%2C%20once%20for%20each%20number%2C%201%20through%20X%3C%2FLI%3E%3C%2FOL%3E%3CP%3ENow%2C%20if%20that%20doesn't%20make%20any%20sense%2C%20come%20back%20and%20clarify%20what%20doesn't%20make%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20unless%20the%20four%20spreadsheets%20are%20themselves%20somehow%20dynamically%20different%20for%20each%20addressee%2C%20%3CEM%3Eis%20there%20some%20other%20reason%20why%20you%20don't%20just%20do%20this%20as%20a%20MailMerge%20using%20Word%3F%3C%2FEM%3E%20You%20appear%20to%20be%20familiar%20with%20it%2C%20and%20it%20wouldn't%20require%20you%20to%20do%20anything%20fancy%20with%20LOOKUP%20formulas%20and%20the%20like.%20Word%20documents%20can%20replicate%20tables%20and%20pretty%20much%20any%20other%20static%20array%20of%20information%20that%20Excel%20can%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi there.

 

I have an excel document with 4 sheets. I'd like an address from a database (this data base can be turned into an excel sheet) to print on all 4 sheet, in a specific location. On top of that I'd like to make multiple files in the same format but with different addresses. Similar to mail merge in Microsoft Word. How do I do that quickly.  

1 Reply

@Suraqah 

 

It's a bit difficult to picture exactly what you're looking for, so what I'm going to suggest may miss the mark entirely. I'm also assuming this is a one-time affair (which is to say, I'm going to give you some steps that probably could be automated at some point via a VBA routine; but I'm not going to go that far).

 

OK. Here's how I would approach what I think you're describing.

  1. Take that database and make it an Excel table in the same workbook as your three four sheets. I'm going to assume that the database is set up (and therefore the Excel version will be too) with first name, last name, street address(es), city, st, zip....all those as their own columns nicely laid out in the format of an Excel table
  2. Add a column with numbers from 1 to X, whatever your maximum count is
  3. In the appropriate cells of the four spreadsheets, write VLOOKUP or XLOOKUP functions functions to retrieve the full address based on reference number off to the side in the first sheet.
  4. Run through the numbers, printing the sheets or whatever you are planning to do with them, once for each number, 1 through X

Now, if that doesn't make any sense, come back and clarify what doesn't make sense.

 

By the way, unless the four spreadsheets are themselves somehow dynamically different for each addressee, is there some other reason why you don't just do this as a MailMerge using Word? You appear to be familiar with it, and it wouldn't require you to do anything fancy with LOOKUP formulas and the like. Word documents can replicate tables and pretty much any other static array of information that Excel can do.