have order form in excel 2003 and want to link customer details to an address databas

Hi Adasca
Yes, its the IFERROR function. 2003 has an equivalent IF(ISERROR( that does the same job, except that the formula is twice as long. This is because you have to enter it separately for true and false on the error test. The introduction of the IFERROR in 2007 was effectively amending bad programming as the the coding for true and false would never be different. I'll need to change to the other error function and resend. Based on what you could see before amending N5 did it look promising ?

Herc
 
Yes - I think its on the right track. I should have split the name into two cells though, one for 1st name and the other for surname if its not too late to do that.

Thanks again
 
Hi Andrew
Regarding the names, the database has full name, and also first and last name (separately). If you want the separate names, you just need to modify the invoice template and then alter the formulae in it to pick up columns S and R instead of T.
Ive altered the formulae to take out the IFERROR function, and it works OK on my XP box thats running Excel 2003.

Hercules
 

Attachments

  • invoice templateP.xls
    268.5 KB · Views: 24
Thanks ever so - this is the sort of functionality I was after. But to make it perfect, is there a way for a dialogue box to open on the service invoice worksheet? So, for example, by typing the letter s for smith (say) in the name cell, the table with the choices beginning with s appears as a form on top of the service invoice worksheet rather than on the accessname sheet?

Possible or difficult?

I am ever so grateful for your help Herc.

Best
Andrew
Sussex
(ps semi-honorary Yorkshireman as I used to live in Grassington)
 
Andrew
I can't think of a way to do what you ask without redoing the whole thing as a VBA module, with your service invoice becoming a VBA form. Personally I wouldn't go this way to gain a small benefit unless you have someone to provide VBA application support to update your system in the future.
You could have everything on one worksheet, and place the extract table next to the Service Invoice template, but that doesn't do what you are really asking for.
The closest is probably a drop down list that you could place on the template, but this would offer you a full list from the database rather than filtering as you enter. I think what you have is probably as good as I can get it.

Best regards

Herc
(A Lancastrian living in Yorkshire, actually) :eek:))
 
Hi Herc

Apologies for not getting back before now as I have been away from this. My database has over 3000 entries and when I tried amending the formula to change the range I managed to break the template. I have managed to replace your entries with mine (for the first 50 or so) but still get a funny with the first record coming up as record no 3 even if it is a completely different number. Any thoughts? I have included the spraedsheet so you can see what I have done. Of course, when I get it all working I would need to finally transfer the code to the first page and have I39 as the entrybox.

Thanks again

Andrew
ps - The border near Skipton has moved backwards and forwards from Lancs over the centuries I gather
 
Hi Herc

Apologies for not getting back before now as I have been away from this. My database has over 3000 entries and when I tried amending the formula to change the range I managed to break the template. I have managed to replace your entries with mine (for the first 50 or so) but still get a funny with the first record coming up as record no 3 even if it is a completely different number. Any thoughts? I have included the spraedsheet so you can see what I have done. Of course, when I get it all working I would need to finally transfer the code to the first page and have I39 as the entrybox.

Thanks again

Andrew
ps - The border near Skipton has moved backwards and forwards from Lancs over the centuries I gather
 
Last edited by a moderator:
I hope you haven't posted genuine phone numbers in that example

Thank you for your advice. The numbers do not relate to the individuals but it would be better to remove them. CAn you please advise how I can remove the attachment from the thread?

Andrew
 
Hello Andrew
Sorry - Ive only just seen your last message.
(1) The problem with the value returned in Q4 is because you have forgotten to insert the braces in this cell after you last edited it (maybe accidentally). To fix, click cell Q4, and in the formula bar, click anywhere in the formula to select Edit mode and then press Control+Shift+Enter and this will insert the missing braces making it an array formula again. I think this is the only cell in error.
(2) To make the name change in the invoice template:
; Selected A41:B41, [right click] Insert + Shift Cells Down ; To extend the template by one row
; Selected B41:I41, [right click] Format Cells +Alignment + Merge cells ;
; Relabelled A40 = "First Name" ; A41 = "Surname"
; Amended Formula in B40 to =SelectName!S4 ; B41 to =SelectName!R4

Hope that does it.
Hercules
 
Thanks Herc

Still have a small logistic question. If I have say 6 entries for Smith, how can I select the correct one as it seems to be the first entry that will populate the invoice sheet. Also, if the customer is a new one with no name showing, I thought I could use the data entry form on the SelectName sheet to add the new customer details and then do the select name but would the sheet resize the array to include the new line do you think? Will I need to change the formula each time I add a new entry?

Thanks again. Andrew

btw I thought I had sent an email to Ken Puls requesting my sample data be removed from the thread but have not had a reply so maybe I didnt send it correctly. Do you know how to remove the file as whilst the names are real and the numbers are jumbled it would be best to remove it.
 
The last posted example has extra header cells in the middle of the data.

If you refer to the inbuilt Data Form then remove that line & the form shoyld work providing you select a cell within the data
 
Andrew
(1) Multiple Smiths - The search key (N5) needs to be unique. You could use full name, or create an extra column (e.g. Smith107, where record number 107 is the Smith you want.
(2) New records - If you extend the database from A2:K51 to say A2:K5001, this would give you 2000 blank records to accommodate new entries. Because they are already indexed, new entries will be included automatically, and you would not need to alter anything again unless your database exceeds 5000 entries.
(3) I agree with Roy - For tidiness, I wouldn't mix header labels with the data records
(4) I have PM'd Ken Puls about the attachment, so hopefully he will get back to you. I don't think users on this site can remove their attachments once they've been posted.

Hercules
 
Instead of extending the data like that, creaate a Dynamic named Range or if your version of Excel supports it then convert it to a List.
 
Adesca, I have removed the attachment. Sorry it took so long, but I never got an email or message from you, and I was away from my computer when Hercules PM'd me. Should be good to go now though. :)
 
@Roy
I didn't go for named range because in order to keep the formulae up to date I thought that you would need (in this case) 11 different ranges for cols A to K. Also, when filling in a new record if some columns are blank, would the ranges for those columns be extended, bearing in mind that the index arrays need to be the same size? Can it be done your way avoiding these problems ?

Cheers

Herc :eek:))
 
Looking at the example in Post#24 I would remove the empty row in the data then try converting the data to a List in Excel 2003, a Table in newer versions.
 
Hi Andrew
Had a bit of spare time today, so Ive gone back to my original demo (no real telephone numbers, promise :)).
The main changes are
1. Extend Database to 5000 records
2. Build in extra hidden column B to sub-index duplicate surnames. Hidden to reduce risk of formulae being overtyped.
 

Attachments

  • invoice templateP_1.xls
    284.5 KB · Views: 13
Back
Top