Macro to reference data from one sheet to another

Hello all!

I have a project that I'm going to attempt to handle, unfortunately I have no idea where to start. So any ideas or input to get me in the right direction would be greatly appreciated!! I am new to macro programming in excel and have a very light background in programming.

What I have:

Right now I have a data file(Sheet1) that lists the data of various accounts. Each row is a different account and each column is data that pertains to the account. Ex. Row 1 will be account # 1, with things like name, account number, address, etc... in columns A B C and so on.

What I want to do:

I need to make forms for EACH account in another sheet (sheet2), and in that form i need to reference specific data from Sheet1. THe reason it needs to be referenced is beecause if we make changes to Sheet1, the form will also need to be updated. Also, I dont need all the data in sheet one for each account, I will be pulling out data from different columns, ie. only columns A, E, F, etc...

Please let me know if anything is unclear and if you can help in any way! Thanks

Hi

Rather than having a form for each and every account, why not have one form, be able to select the account, then bring in the data relevant to that account? Can be coded with functions and doesn't need to have any macros.

Perhaps build an example workbook with 2 sheets. Sheet1 has representative data, and sheet2 has a mock up of your form. Attach this to the post so we have some idea of what you are working with.


rylo

Thanks for the response Rylo!

The reason why I need a form for every account is due to the fact that evenetually I will need to be able to scroll down and look at each and every form to make sure everything is correct. Thus it would be nice to have an individual form for each account. I will also be printing out all these forms at once. In the end there will be over 1000 accounts and thus 1000 forms.

I've attached a mock up of what I'm currently working with. The Data File Sheet contains all the information while the form will only display specific data from the data file. The data file will be updated on a continual basis, and in turn I will need a macro that will be able to update the form based on these updates to the data file sheet.

Thanks for any help you can provide!

I also forgot to mention that I will be needing to make absolute references to the data file, as cells in the data file may move around etc (by sorting/filtering)

Hi

I'm running Excel 2003. Can you convert the file, and reload.


rylo

HI

Can you fill in one of the forms (say the first one on the Account Sheet) showing where you want the output data to go. For instance, the Tracking number could go into B3 or B6. Just so we can be sure where the data is coming from, put in formulas showing the source. So if the tracking number was going into B3, then the formula in B3 would be ='Data File'!A2

Just remove the existing sample file, and attach an adjusted one.


rylo

File has been attached

Hi

See if the attached gets you there.

rylo

Hi

I've added some rows to the code to format the rows and columns.

See if that fixes things for you.

Please Login or Register  to view this content.

rylo

Works great!! Thank you for all your help!!