Cell value based on another cell on another sheet 1

[TIgerV]

I have a list on sheet one that looks like this:
Mr. Haskins;912603324664;C_00;IC_1;OC_0;5;3;CONFIG;;
Mr. Jones;912603123664;C_00;IC_1;OC_0;5;1;CONFIG;;
Mr. Blast;912603123664;C_00;IC_1;OC_0;5;2;CONFIG;;
All values are in column A. Additionally, there are the same number of ";" as it is a delimited file.

Meanwhile on Sheet5, I would like cell B3 to equal "Mr. Jones;912603123664" based on the fact that following the "OC_" value, we see "5;1". (Page 5, Button 1).

I will continue on to create "Mr. Blast;912603123664" in cell B5 based on it being button 2 (OC_0;5;2). There are actually 50 buttons, but I will get them after i find the initial piece.

The 2 numbers following the "OC_" are unique. There is a "6;1" and a "5;1" but never two "5:1"

For Extra credit, I would like the "OC_" value to set the color of the cell, based on a table. So, 0 is blue, 1 is red, 2 is orange.... But this isn't completely important.

Thank you!

--TIger

 

[SkipVought]

Hi mr instructor handing out "extra credit" questions.

Why haven't you done a Text to columns yet?

And what's the logic for button 1, destination B3; button 2, B5? (Implied button 3, B7?)

"Actually 50 buttons."
Well I like to get ALL the requirements up front so I can ask project-related questions, since I've been nickel-and-dimed too many times. Lets get it all out so we can see the scope AND PURPOSE of what you've got.

I'm still asking myself, "why hasn't this tiger taken the project by the tail, by parsing his input values so they can be much more easily manipulated?"

So ANSWERS. We need ANSWERS.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TIgerV]

I didn't do "TEXT TO COLUMNS" because I need the data in it's current format. This tool is going to some people who are not very computer centric, and If I have too many steps in the end product OR if it's too complicated, then the project isn't worth it because it won't be used.

If it could be automated without code, it'd be completely fine. But the data, in order to make the tool more usable, needs to stay in this format. It gets reimported.

If I get the one formula, I'll be able to hit the other 49.

The application is this: People export data from their system, import it, update phone numbers, then each worksheet represents a page in the system. They can pull up page 5 and see "MR Jones;912603393999" on button one, and then I'll have an option for them to move it to button 4, or wherever. Changes on that worksheet are reflected in the original page, and they re-import to the system by copying and saving in a text file.

Macros and code will not allow the users to share the workbook due to network security requirements that are absolutely crazy.

Tony

--TIger

 

[combo]

I'm not sure what you mean by "button", concerning the rest, if:
[ul][li] the semicolon separated data structure is fixed,[/li]
[li] output to range,[/li]
[li] there is table-table and row-row correspondence between input and output,[/li]
[li] there is clear logic for processing,[/li]
[li] people accept input and output from structured tables,[/li]
[li] people accept refreshing data manually with right-click and selecting 'refresh' from the cell' popup menu,[/li][/ul]
then I would use power query available in excel 2016+.
The steps:
[ul]
[li] create table from input data,[/li]
[li] table to query in PQ,[/li]
[li] new query in PQ environment, split data, process columns in consecutive query steps, output to worksheet.[/li]
[/ul]
This process may result external data security warning. BTW, you haven't answered important Skip's questions, among others: "And what's the logic for button 1, destination B3; button 2, B5? (Implied button 3, B7?)"

combo

 

[SkipVought]

Well, with your stipulations of how the data should be presented for each "page"

I constructed formulas on sheet5.

The desired values are in B3,5,7...

In column C I have put 5 in Row 1 and named C1 as "page"

In the formula bar you can see the formula in C17, the same formula in C3:C17. This is the LOOKUP VALUE for each row to get the value in column E from which I get the value in column B.

Here's how I modified sheet1

I added formulas in columns
B is the LOOKUP RANGE
C:K are the FIND() offsets for the delimiters.

Sample file is uploaded.

Have fun!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[TIgerV]

Thanks to all!
Skip's response was the answer I was looking for.

--TIger

 

[SkipVought]

Glad this helped. Post back if you need further help or clarification.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

I corrected a small misstep, where I assigned C1 on sheet5 as a Named Range, page.
[tt]
C3:Cn =IF(MOD(ROW(),2)*INT(ROW()/2)=0,"",[highlight #888A85]page[/highlight]C$1&";"&MOD(ROW(),2)*INT(ROW()/2))
[/tt]
Consequently this formula, can be copied to any page-intended sheet, where the page number can be manually entered in C1.

BTW...
1) All the formulas are designed to COPY from one cell or a one-row-horizontal range of cells and the PASTE into a vertical range of one or many cells.

2) If it were me, I'd use the DATA>Get External Data>From TEXT files to IMPORT on REFRESH, fresh data into sheet1 WITHOUT SPECIFYING A DELIMITER. There is a feature that can propagate the formulas to every row of data. Otherwise you'll need to COPY n PASTE.

3) Expanded the sample to include a sixth page. Takes just a few seconds to set up (COPY n PASTE) a new sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein