excel auto populate

[motox2]

Hi all, im looking to auto populate cells in excel from another excel worksheet to help save time. I have an huge excel main database file with numbers in column A and names in column B. what im trying to do is use that database so when i create report spreadsheets all i want to do is enter the number in one column and let the name autopopulate into another column for me pulling the name information from the main database file. any help with this would be greatly appreciated!!! Happy Holidays

 

[GlennUK]

Have you used VLOOKUPs before?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[motox2]

actually would microsoft access be a better solution to this scenario?

 

[Gavona]

If you can combine your two tables in Access first, or do the whole job in access then it would be a better tool - especially if your report is large. However, for various reasons that option is not open to me. So my thoughts about excel based solution:

If you have a large report to populate then thousands of cells with vlookups can slow things down a lot. I do this a lot with multiple columns having lookups to other files. But I have a simple VBA assisted routine whereby I:
Set calculation manual,
Copy the formula down one column at a time (typically 6000 rows)
calculate that column only and convert to values before moving on to the next.
Set calculation automatic

The workbook containing the source data must be open or things could freeze. (Again, I have vba to open all linked workbooks).

(In the past I played a little with Data, get external data, new database query to set up a relationship and get the data from my two tables into a single table in excel but that didn't work efficientlt for me.)

Gavin

 

[SkipVought]

You do not necessarily need to use MS Access to combine data from multiple spreadsheets. I do this often right within the Excel workbook, where I might have 2 or more worksheets. If I need data from another workbook, I first query that workbook, returning data to a new sheet, and then combine my data using another query.

faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}