EXCEL Problem......

[Deadshot]

Hello!

Having a little issue with my IT SAC

The situation

I have a database filled with client information of a shop, and the staff need to be able to print the clients details out for them. So I created a new sheet called "Print Customer Quote" which is nicely formatted for printing. This is currently Hyper linked from the "database" sheet.

What I need

Instead of using a Hyperlink to the quote sheet, which then requires the staff member to re-enter the Client ID into the "Customer ID" cell to bring up the clients detalis, I would like a button function (see Image) that can pick which Row it is in and look at the Client ID and then place it in the "Print Customer Quote" Sheet when clicked and then take the staff member to the sheet at the same time. So printing can happen faster and there be less chance of error.

Can this be done? I have explored Macros with no success.

Thanks!

[Lasercookie]

I try to avoid messing around with macros in the middle of SAC, as they can be quite time consuming. A macro like this might be a bit messy (or require coding for the easy way, again a waste of time).

1. I would opt to use a drop down box for the client ID instead, and then place the 'print' button next to that. Drop down boxed are pretty user friendly. Since the quote will change to tell you the name of the customer, this way the user is provided with feedback that ensures that they have selected the right customer.

2. If you wanted to provide better validation, you could change the "Client ID" to the "Client Name". If you do that you would have to think of something to solve the problem that there might be duplicate names.

3. Or I guess you could stick with the VLOOKUP's being based on 'Client ID' and rename the Client ID to something that identifies the name better (e.g. VIDEOEZY or like a student code like thing). I would still provide the drop down box though.

4. Another option would be to, in the top right corner of the database worksheet, have a 'print quote' box. Here you could move that 'client ID' drop down box to there, and then have your VLOOKUP's work off that cell. Next to that, put your print quote button. This option gives that faster printing you want.

Option 4 sounds like the best option to me, but it does separate the reference cell from the quote worksheet. Otherwise, I would go for Option 1.

Either way, I still strongly suggest to avoid macros like the plague.

[Lasercookie]

If you do want to know how to do that macro button thing, the 'row' function can tell what row a cell is in, column does the same thing for columns, there's also offset, which can retrieve a value. HLOOKUP also sounds promising. But if you're going to use those functions, you'd end up having to use multiple cells and it wouldn't be nice and clean.

That's just my guess about how to go about it using macros (from what I know). Somebody else probably has a better approach of doing macros though.

[Deadshot]

Hello

Would a drop down box be able to update as new clients are added? and what would happen when the say the business expanded to 200+ clients? I feel that that would create more issues.

And I have made those suggest changes to the Client names, it looks much better now.



Thanks


 

[Lasercookie]

Would a drop down box be able to update as new clients are added? and what would happen when the say the business expanded to 200+ clients? I feel that that would create more issues.

That is a valid concern. But consider this, on the quotes page, I'm assuming you're using a VLOOKUP to retrieve the data. That VLOOKUP is set to a specific table array (as an example, say A2:C6). So technically, the VLOOKUP as it is would not function if a new client would add.

If you're using the excel naming ranges thing, then it is relatively easy to fix up the VLOOKUP for new clients. E.g. you set the A2:C6 to be named DATABASE. Subsequently your formula will look something like: VLOOKUP(F2,DATABASE,2).

Now if you add a new client, you can redefine "DATABASE" to refer to A2:C7, which will now include the new row; leaving all the VLOOKUPs functioning correctly. Better yet, you could use a dynamic named range that will automatically update when a new row is added.

This is the same case with the drop box, it is set to a specific set of cells, but you can set it to refer to a dynamic named range. This removes your qualm about drop down boxes not being flexible.

I hope you have been using dynamic named ranges for your VLOOKUP formulas already. If not, time to fix that (or leave it for the testing table).