Excel Quote template

[people3]

HI All,

I'm after creating a quote template that has two sheets.

The first sheet is were the product info and pricing is (including our pricing).

The second Sheet will be a printable quote to send to the customer so exludes our prices and other fields.

The problem i am getting is that on the first sheet the number of rows changes, it may just be a couple of items to over 100 rows.

How can i get the second sheet to take the data from the first that i need but having a varied number of rows?

I hope this makes sence.

Many thanks

 

[SkipVought]

Hi,

You need to use LOOKUP functions rather than cell references, that you have discovered, move around AS THEY SHOULD.

Check out the VLOOKUP function.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[people3]

HI,

Thanks for the reply - I am using VLOOKUP to get the data which is fine,

The problem is on the first sheet how do i make the number of rows varialble to match the second sheet.

if the second sheet has 50 rows how can i get it to insert 50 rows of data.

If for the next quote contaions 20 rows how can i get it to insert the 20 rows using the same formula.

Sorry if this is not making any sence

 

[SkipVought]

That makes no sense at all to me.

A quote might use only ONE item from your lookup table or 100 items.

You don't make rows "MATCH"

Rather you lookup values in your lookup table and teturn those values to your quote.

Maybe you should post a brief example showing some rows from your lookup table and then what data you have on your quote and describing what you need to lookup.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

The first sheet is were the product info and pricing is (including our pricing).

also...

The problem i am getting is that on the first sheet the number of rows changes, it may just be a couple of items to over 100 rows.

P3: Are the items on Sheet1 specific to each individual quote? I think Skip's responses assume Sheet1 is your complete price list and you just need to pull the pertinent prices over to Sheet2 (which strikes me as the correct way to go).

Please clarify.

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[SkipVought]

Yes, it is absolutely the best way to go. Regardless of what you are quoting, you will always have the data at hand to complete a quote, without extra import steps. You might wabt to consider the inventory/price table to be in a separate workbook. You can still use lookup function while that workbook is ALSO open. No need to worry about confidential company data being in the same workbook that you might send to a customer.

There are other more sophisticated data acquisition options as well, that may need VBA (macro) code. If you need to pursue a more robust solution, post in forum707.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}