I want to get data from one column in one worksheet in to a different worksheet using a dropdown.
I have my invoice on the first sheet and the data on a different sheet.
I highlight a blank cell on the data sheet then click on the first cell with data. I can see the "name" reference. But if I click on two cells with data, the "name" disappears.
I copy the "name" from the single cell, then define a name for it through the formula tab.
Back to the invoice sheet... I highlight one cell, then data, data validation, list, F3 to get the name then OK.
BUT... the cell on the invoice sheet only shows one item from the list - not a dropdown in sight.
Where am I going wrong, please?
Thanks
Chris
Apologies - the heading was wrong - it should have read Data Validation across two worksheets
The file contains many invalid and incorrect named ranges. Each column in the new learner sheet is formatted as a separate table, though I believe the rows are related. If so, start by creating one table, for example NewLearners and create named ranges for each column in that table. Then you can use those named ranges as list items for some of the DV cells in the invoice. I would get rid of all the other ones and start from scratch, avoiding conflicting named ranges. It shouldn't be too much work.
Then, in D9 of the invoice, I think you want to refer to cell C20 (learner short code) and another cell for the tutor short code (not currently in the invoice) of the invoice, rather than hard coding to E7 and A7 in the 'new learners' sheet. Fot the tutor code you could also look it up similar to what you did in B18 where you look up the town for the school in B17.
The dropdown for PO numbers is linked to the tutor short codes instead of the PO numbers list.
In C21 you can also enter a similar formula as in B18.
I believe this will also resolve the questions in your earlier post, that I accidentally deleted. Please accept my apologies for that.
Thank you - worked perfectly.
I have now amended each of the colums on the Schools and Data sheets which I hope makes things clearer.
On reflection, what I was trying to do was to have a kind of "if" invoice...
The trouble is there are so many variables!!!
If we start with the school, the dropdown would populate the vendor number (blank if not needed) and the purchase order (again blank if not needed). The learner from a dropdown would be next... that would automatically bring up their date of birth if it was required by the school (and leave a blank cell if not), and bring up their tutor's full name (learners can have different tutors; tutors have more than one learner!).
We can fill in the Subject and session dates etc cells.
The invoice number is calculated from: the company and region (HSTW).theyear.themonth.thetutor's initials.the learner's initials.
We run a similar business (HSTSK) in a different area with different learners, schools and tutors - although there are some tutors who work for both sides of the business. But as we run separate accounts they can be "duplicated."
Would this be a better way? One problen I foresee is that with the dropdown way ther are so many learners, if they had initials at the end of the alphabet, it would be a chore scrolling down.
Also,we would have to align the Learner list with the Schools list as learners only go to one school
I would be grateful to hear your throughts.
Thanks
Updated file attached
Much improved, though I couldn't resist helping out a bit more. In the attached file I cleaned up the invalid/incorrect named ranges and applied data validation only to the learner and school's name. The key is to start with the Learner. Then the dob, tutor, invoice#, and PO# will be filled automatically. Then, select the school's name and the town, vendor# and terms will follow automatically.
Note that I've used INDEX/MATCH formulas that look up the correct attributes for the learner and the school. I also got rid of merged cells as they are a real pain in the a....
Some formulas return a zero, but I've suppressed these with a custom format.
See if the attached file works for you. Let me know if I've missed something.
Fantastic
Wonderful
Superb
Can you help with VBA as well?
Best wishes
Chris
Sorry, but VBA is definitely not my thing. Perhaps someone else.
