Linking Pages

[Mikeyboy]

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

[teylyn]

This does not require macros. With proper (= fit for the purpose) spreadsheet design, it can be done with formulas, performing much faster than any macro.

One sheet to hold all the codes and associated values.

Another sheet where you enter the code and a Vlookup grabs the associated value.

No need for several sheets, but if need be, these can be incorporated.

Post a sample file with dummy data to mask confidential information if you need help setting this up.

cheers,

[Mikeyboy]

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

[GeneralDisarray]

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:

=VLOOKUP(item_number,'Letter_code'!$A9:$I10000,2,FALSE)

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:

vlookup(item_number_address,RangeEETF,2,false)

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

[Mikeyboy]

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

[GeneralDisarray]

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.

[Mikeyboy]

Thanks General,

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

Cheers,

Mike

[GeneralDisarray]

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?