• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown or wirting with vlookup in same cell

ExcelMan7

ExcelMan7

Member
good day gents,

kindly please help in attached excel sheet (formula).... i explained inside.

thanks
 

Attachments

  • Offer Form.xlsx
    73.7 KB · Views: 14
It is not very clear as to what you want from the explanation in the worksheet but one thing you are doing is using merged cells.


Originally I need let this cell read from Reqs for offer Sheet and if there is change I want select from the Drop-down list or I can edit by writing the new position and next offer I want comeback to the original witch read from Reqs for Offer.
Click to expand...
If you want your drop-down list to expand to take new entry's in the Data sheet turn it into a table

Avoid merging cells
Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.
In addition, not all cell formats stick once you unmerge a cell.
You can't sort a column with merged cells.
You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.
You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!
Merging cells in columns and rows could lead to data lose, bad thing.
Formulas and Functions that refer to merged cells will not work, bad thing.
Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cells problems.



.
 
bobhc said:
It is not very clear as to what you want from the explanation in the worksheet but one thing you are doing is using merged cells.



If you want your drop-down list to expand to take new entry's in the Data sheet turn it into a table

Avoid merging cells
Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.
For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.
In addition, not all cell formats stick once you unmerge a cell.
You can't sort a column with merged cells.
You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.
You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!
Merging cells in columns and rows could lead to data lose, bad thing.
Formulas and Functions that refer to merged cells will not work, bad thing.
Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cells problems.



.
Click to expand...

thanks a lot but might i forget to put the formula originally the Merged cell contain vlookup "=IF($M$9="","",VLOOKUP($M$9,Req,5,FALSE))" to give the Job title (position) but some time i need to change the position (job title) from the form either by drop down or write the new position and not going back to the original data to change .... then Next Offer i want come back to original again which is Vlookup.
 
Ravi Verma said:
Hi Admin,
Pls update more data in 2nd sheet and let me know what output you require.
Regards,
Click to expand...

Hi Ravi ,
thanks for trying to help me....also thanks for every one who will try to give me from his exp... i updated the Sheet and i tired to explain what output i want in Green background .

thanks
 

Attachments

  • Offer Form.xlsx
    74.3 KB · Views: 9
Hi Admin,
my question to you is :--
where you need to enter data and what fileds need to be auto-populated ...
do you need to enter requistion number and hence need to get position code , Grade code etc to be populated etc..

as per the formula , i guess you need to enter data on requition..

please help me understand your requirement .. and also request you to kindly get nmore data updated in "Reqs for offer " tab.

Regards
 
If you quote full posts in your reply's your thread is going to get very long and tiresome to read, which will put of potential help.

Quoting is generally used to highlight a section of post to emphasis a problem.



.
 
bobhc said:
Your requirements are not very clear from your explanation, are you wanting other drop down menu's dependent on what is chosen in the first, if so this link will be of help to you.

http://www.contextures.com/xlDataVal01.html
Click to expand...


thanks .... i know how i can do Validation ....see below . attached again ... thanks for help
in F23 i put vlookup "=IF($M$9="","",VLOOKUP($M$9,Req,5,FALSE))" to give me the Job title of Requisition (M9) from Sheet(Reqs For offer)which is E2 but some time i need to change the position (job title)which is F23 from the same Cell either by drop down (i put drop down but as you know once i select from it the vlookup formula will be reomved ) or by write the new position and not going back to the original data in "Reqs for offer Sheet" to change .... then Next Offer i want come back to original again which is Vlookup... Goal : i want Vlookup dont be removed once i select from Dropdown or write new Position in same cell ..
 

Attachments

  • Offer Form.xlsx
    74.3 KB · Views: 9
You must log in or register to reply here.
Back
Top