Help with complicated look up

[GAVGAV27]

Hi guys can anyone help me with a very complicated lookup I am trying to write

[Russell Dawson]

Hi

There's always somebody ready to have a go. Upload your workbook if you can and explain exactly what you are trying to achieve.

Russell

[FDibbins]

gavgav, try the attached and see if its what you need?

[GAVGAV27]

That is great,

i have re attached the sheet in its final format, I have made a few changes and removed the client tab as it is really the same as the order sheet. so i added 2 extra columns x and y and copied the formula into there. I forgot to mention that the stock price would never be charged to the client so i added an extra row in the prices tab for "n/a" and have adjusted the formula for that. I have also added some conditional formatting to order sheet column a, that will help highlight issues with client orders.

I can't thank you enough this usually takes me 4-5 hours to process this will take seconds, what i will do with my free time i do not know.

I have a couple of final areas that would really finish this sheet off. i hope you don't mind trying to help me one last time.

1) I have copied the formula down to row 950, as i stated the orders are usually between 800 - 900 lines. Is there away the sheet can reduce or increase itself to only match the size of the order. if not i can just delete the unused lines it won't take long.

2) if i paste the order when i receive it into the order tab is there a way to make it copy over into the us tab or will I have to paste it twice.

3) can you check my formulas in the summary tab i think they work.

4) I have tried to lock the top row so that i can see it as I scroll down, but it locks the top ten lines, is it possible to just do the top headings line "row1"

5) I have clicked ignore errors as it kept saying that empty cells were involved, it made no difference and removed the green tag in the corner of the cells is this the right thing to do.

6) Is there away to remove "value!" in summary sheet, i know its only there until i copy the order info in but would just like the sheet empty if possible, i tried conditional formatting it but with no joy.

7) separate to all of this along time ago when i could remember how to use excel i remember macros could be assigned to a cell so if you clicked on the cell it ran the macro can this still be done. I would like to add some print macros to the summary sheet so if i click on them i can print all sheets or i i click another it prints client sheet.

8) last but not least is it possible to link excel to word. if i add a few extra details to the summary sheet i would have everything i need to produce our purchase orders and invoices that i currently do in word. can word extract data from excel to fill itself in, or i am just pushing it too far.


I seriously can't thank you enough for the speed you have replied with and the help you have given me, is there somewhere i leave comments on your help etc.

[FDibbins]

im glad that you are happy with what we have achieved so far I will start working on your "wish list" and get back to you with any questions i have

you can thank me by following point [3] below

[FDibbins]

1) I could probably come up with something, but it may be easier to use a filter to just hide the blank rows. i have added this to your file, let me know if you need help using it?

2) I think i have what you need, just not sure if i took it across too far of not

3) formulas fixed

4) titles fixed (in 2007, put the cursor where you want the titles to be fixed, go to view, freeze pains)

5) i have modified the formulas to manage "no" entries (sorry, should have done that to begind with)

6) fixing 3) fixed this

7) yes, this functionality is still available and very useful. what I would suggest is to use the "record" (or learn) mode. it records everything that you do, and stores it as a macro that you can use later. to enter "record" mode, look at the bottom left of the sheet, just to the right of the "ready" icon...the "record" icon looks like a worksheet with a red dot in the top right corner. I would suggest if you want to try this (and it really is easy), practice a few times 1st, because therecorder records everything, even corrections. Once you have the macro recorded, the next step is to insert the "button" and then apply the macro to it. on the insert tab, select "shape" pick a shape (any will do), and put it onto the page where you need it. once you have it on the sheet, right-click on the box and select "assign macro" and pick the macro you want. You can add whatever text you want, to the macro button.

8) follow this link to learn how to link excel to word

http://wordprocessing.about.com/od/m...ingexcel_4.htm

Let me know if you have what you need, or if there is anything else I can help you with?

[GAVGAV27]

Once again thanks,

I was working on the sheet when you replied and i have managed to update my sheet with everything you did except fix the formulas in summary so it does not show value!, sorry i was just expecting you to tell me how to do it.

the one last thing i would love to be able to do is to list the issues on the order sheet in the sheet issues that I have just added to the tabs at the bottom. Is it possible to search the order column A and B and if it is blank or says tbc copy the whole line into the issues tab. I will not be working on the sheet so you can update it straight into that if theres anything you can do.

I really do think that is as good as i can get the sheet and it will be a great help, at present there is nothing more i need from the sheet. If you can manage this it would be great.

Thanks for all your help and answering my questions
Gav.

[FDibbins]

I had made some improvements in my version of your file, and have been trying to incorporate them int your updated file. i noticed that you cad changed your "item" description from a,b,c etc to actual words. you need to make sure that these match, otherwise the lookups and matches wont work

I have applied a filter to your orders sheet, you can use that to hide blank entries, and also to pull out all "issues" that you want to identify. click on the "pull-down" and select what yo want to see (or unselect what you dont)

[GAVGAV27]

hi,

yeah changed the look-ups and everything is working fine, have added the filter to mine that helps. the only thing i cant resolve is the formula returning #value!, you said that you set them to manage "no". how do you do this? apart from that its all ready to go. Thanks for all your help, i hope you are available the next time i need some assistance.

gav

[FDibbins]

not sure where you would have anything resulting in a "no", send your file back and i will check it out?

and yes, i will be only too happy to help with any future questions you have