Linking Pages

I am really struggling to get Excel to work in the way I want it to.

We currently use a Microsoft Excel spreadsheet to produce quotations.

The workbook has several tabs which each comprise of different work elements. Each tab has a long list of codes in one column with prices and description in the adjacent row.

The final tab of this document is a quotation page specific to each job.

At present we have to look the code up on each tab and copy the row to the quote page. When there are lots of elements this takes a long time.

I want to be able to type the unique code in on the last tab and the associated data to automatically appear in the adjacent row.

This will greatly speed up the quotation process.

Is this something that can be done with macros?

Any assistance would be welcome.:confused

Thanks,
Mike

Thank you Teylyn.

Attached is a sample. There are many more tabs involved.

You will be able to see the code.

Ive also attached a word doc which explains what I want to do a little better.

Any help most appreciated.

Thanks,

Mike

Hmmm...a sample workbook would be better. Teylyn is 100% correct, spreadsheet design would eliminate the macro need.

I suggest 2 possible approaches:

1) Name the Sheets according to the 4 letter Codes (like EEF), You could then reference the sheet name in a vlookup formula:

Please Login or Register  to view this content.

So, where I wrote "item_number" you would place the Address of your item (on the quote sheet) and where i put "Letter_code" you would need the address for the EETF type entry. ( i think you would have to build the string """ & A1 "'!$A$9:$I$10000" to actually reference that range of a given sheet).

2) possibly easier, Name the table (looks like A:I in your attachment cover the information needed) with a Named Range matching the EETF type of letter code.

Then the Vlookup function could reference the name of the range (table) and not the sheet, like:

Please Login or Register  to view this content.

anyway, this would be easy to do with an example workbook for illustration. if you upload, i can help get you started :D

Thanks for the advise.

I think I will be showing how little i know about excel but how is the xlsx document i put up different from a workbook?

Mike

Ok, here's the examples of what i was saying...

I had to use the "indirect" function, but otherwise i was on the right track.

Take a look at the attachment, made both methods happen. REmember you will need to adjust your workbook to implement either method. You must either:

1) rename the tabs and use something like method 1.

2) create a named range for every table and use metod 2.

3) find some other way a macro could do this but it's not really needed at all. once you adjust your sheet it will work just fine with the lookup approach.

Thanks General,

I will have to sit down and try to get my head in gear.

Cheers,

Mike

No problem, it might be a good idea to use a macro to name the ranges for you (never done that, but seems pretty do-able when i think it through) and use the second option.

post more if you need help with that. How many tabs are we talking about anyway?