Display rows of cells from sheet 2 on sheet 1?

[Glenjobi]

Hey Guys,

I have an invoicing spreadsheet I'm working on. Sheet 2 has essentially a 'database' that is always being added to. This consists of columns with headers/fields. Running across the rows is the relevant information for each customer.

Sheet 1 is the invoice itself.

I would like to be able to add the details per customer into sheet 2, in database style format. Then pull the information into Sheet 1 to fill out the invoice so I can print to send to customer.

Has anyone had any experience with this kind of thing?

I have attached a simple example here:

http://www.handiliteskylights.com.au/misc/Display_User.xlsx

Thanks for any help!

Regards,
Glen

 

[zapperX]

Hi Glen,
Welcome to the forums.
How are your VBA skills? It looks like what you want to do will require looping through the values in name field of your data base sheet, until you find the name that matches the name on the invoice sheet. At that time you will know the row the data is in and you can copy it.
There may be a way to do it without vba, and if there is I would really like to see it.
Cheers
kevin

 

[cyrilbrd]

Hi,
given data in Sheet2:

Display	Name	Field A	Field B	Field C
n	User 1	1	2	3
y	User 2	4	5	6
n	User 3	7	8	9
n	User 4	10	11	12

<tbody>
</tbody>

data in sheet1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

Name	User 2
Field A	4
		Field B	5	Field C	6

<tbody>
</tbody>

formula in B5 is=VLOOKUP($B$3,Sheet2!$B$4:$E$7,MATCH(A5,Sheet2!$B$3:$E$3,0),0) copied to D7 and F7.

Would that be close to what you needed?

Note:
Make sure the headers are similar on both sheets...

 

[zapperX]

If that works, you are awesome

 

[Glenjobi]

Hi,
given data in Sheet2:

Display	Name	Field A	Field B	Field C
n	User 1	1	2	3
y	User 2	4	5	6
n	User 3	7	8	9
n	User 4	10	11	12

<tbody>
</tbody>

data in sheet1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

Name	User 2
Field A	4
		Field B	5	Field C	6

<tbody>
</tbody>

formula in B5 is=VLOOKUP($B$3,Sheet2!$B$4:$E$7,MATCH(A5,Sheet2!$B$3:$E$3,0),0) copied to D7 and F7.

Would that be close to what you needed?

Note:
Make sure the headers are similar on both sheets...

That's an amazing formula! Well done! It does work, but I think I would have trouble implementing that into the system. I'm not sure how to work it in, so on sheet 2, I can put a Y or a tick box (true/false) next to the customers details I want displayed on the invoice form (sheet 1).

Think that might be asking a bit much. But thank you for your formula, it's awesome, I'll continue to play around with it.

Cheers

 

[cyrilbrd]

Most welcome.
Thanks for the feedback.

sheet1 cell B3 could be a data validation
if column a in sheet2 holds a "n", then cells B5, D7 and F7 could remain blank or with a message such as "not for printing", otherwise it would display data.

for your perusal.

 

[JackHoff]

Is it possible that the same customer name could be listed more than one time, if so, VBA will start to search from the bottom of your database and it should stop at the first instance of that name, will you have a need to re-produce a receipt from a previous sale to the same customer at some point in the past, in this case, the data validation tick or text character will come in handy.