CODEWITHSUNDEEP
exceldatasetexcel-formulaexcel-2013
GrumP
GrumP

Reputation: 1203

Data string manipulation in excel

I have a custom dropdown in excel 2013. It is populated by a dataset saved on a different sheet within the same workbook. Each value is ended with an ID. I want to populate the a-joining column with the "R001" ID value so it has it's own column like so:

ReportName          ReportID
"blahblah R1"       R1
"blahblah E1"       E1

So basically, a user selects a choice from the ReportName Column with the dropdown, and the ReportID column will auto populate with the corresponding ID. I've tried modifying my dataset further to accommodate, without luck.

I've also played around with IF statements, but the approach I have tried is extremely limited:

=IF(RIGHT(B3,2)<>"","R1","")

Can anyone please advise?

Report Name is B1, under that, is the dropdown B2 (which pulls it's content from Sheet2 in the workbook) ReportID is C1, under that (C2), will be the corresponding ReportID, which I can also pull from Sheet2 in the workbook

I need to lock C2, C3, C4 etc... so the user cannot edit, and the number corresponds to the ID of the value in B2. The dropdown which is copy and pasted from B2 as many times as required to B3, B4 (depending on users needs) Will NOT bee in a logical order, so the ReportID logic has to "check" the value in ReportName - to ensure it matches correctly.

Upvotes: 0

Views: 96

Answers (2)

PermaNoob
PermaNoob

Reputation: 869

Not really familiar with excel 2013, but the below works for 2010. If that doesn't work, it should at least be close.

Is your report name always the same length? If so, you could use something like:

=if(b2="","",right(b2,2))

If id length changes sometimes, but there is always a space between the name and the report id, you could use:

=if(b2="","",trim(right(b2,len(b2)-find(" ",b2)))

Then to lock the column, select the columns you don't want to be locked, go to format cells, unclick the locked check box in the protection tab, and lock the worksheet.

Upvotes: 1

Youbaraj Sharma
Youbaraj Sharma

Reputation: 1295

Assuming you have ReportName in ColumnA1(which is a dropdown) and want to populate ReportIDin ColumnB1 (once dropdown value is selected). Also lets assume that you have the above given data in ColumnC1 to ColumnD2. Use the following formula inColumnB1

=VLOOKUP($A$1,$D$1:$E$6,2,0)

Upvotes: 1

Related Questions