trafficman30
New Member
- Joined
- Oct 29, 2015
- Messages
- 6
Happy Monday Everyone
I can make my way around excel (just), but once I figure out a way to get something working, I usually leave it there.
I created a formula to build a Sheet VKUP_PAGE to use as a VLOOKUP table and return one of 3 variants of the following examples in Sheet TESTPAGE
[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD] TEST1 Random bit of text in the middle somewhere Input 30[/TD]
[/TR]
[TR]
[TD]TEST2 TEST3 Random bit of text in the middle again somewhere Output 122[/TD]
[/TR]
[TR]
[TD] TEST4 Random bit of text in the middle Result 1[/TD]
[/TR]
</tbody>[/TABLE]
On Sheet LKUP_PAGE I wanted Column 1 to return the Input / Output or Result (and associated number) and I ended up writing this monstrosity...
=IFERROR(IFERROR(IFERROR(TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Result",TESTPAGE!A1))),TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Output",TESTPAGE!A1)))),TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Input",TESTPAGE!A1))))," ")
In the Column 2 on LKUP_PAGE I return the 1st Text String, eg. TEST1 using the following...
=LEFT(TRIM(TESTPAGE!A1),FIND(" ",TRIM(TESTPAGE!A1)&" ")-1)
So for the first 3 rows, I would get
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Input 30[/TD]
[TD]TEST1[/TD]
[/TR]
[TR]
[TD]Output 122[/TD]
[TD]TEST2[/TD]
[/TR]
[TR]
[TD]Result 1[/TD]
[TD]TEST4[/TD]
[/TR]
</tbody>[/TABLE]
and I use this VLOOKUP table in other pages to call the String in Column 2
example
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]=IFERROR(VLOOKUP("Output 122",LKUP_PAGE!$A1:$B300,2,FALSE)," ")[/TD]
[/TR]
</tbody>[/TABLE]
It all works as expected, but I just think its very long winded.
Any easier option, (VBA included?) would be good to see and decipher.
Thanks in advance.
I can make my way around excel (just), but once I figure out a way to get something working, I usually leave it there.
I created a formula to build a Sheet VKUP_PAGE to use as a VLOOKUP table and return one of 3 variants of the following examples in Sheet TESTPAGE
[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD] TEST1 Random bit of text in the middle somewhere Input 30[/TD]
[/TR]
[TR]
[TD]TEST2 TEST3 Random bit of text in the middle again somewhere Output 122[/TD]
[/TR]
[TR]
[TD] TEST4 Random bit of text in the middle Result 1[/TD]
[/TR]
</tbody>[/TABLE]
On Sheet LKUP_PAGE I wanted Column 1 to return the Input / Output or Result (and associated number) and I ended up writing this monstrosity...
=IFERROR(IFERROR(IFERROR(TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Result",TESTPAGE!A1))),TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Output",TESTPAGE!A1)))),TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Input",TESTPAGE!A1))))," ")
In the Column 2 on LKUP_PAGE I return the 1st Text String, eg. TEST1 using the following...
=LEFT(TRIM(TESTPAGE!A1),FIND(" ",TRIM(TESTPAGE!A1)&" ")-1)
So for the first 3 rows, I would get
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Input 30[/TD]
[TD]TEST1[/TD]
[/TR]
[TR]
[TD]Output 122[/TD]
[TD]TEST2[/TD]
[/TR]
[TR]
[TD]Result 1[/TD]
[TD]TEST4[/TD]
[/TR]
</tbody>[/TABLE]
and I use this VLOOKUP table in other pages to call the String in Column 2
example
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]=IFERROR(VLOOKUP("Output 122",LKUP_PAGE!$A1:$B300,2,FALSE)," ")[/TD]
[/TR]
</tbody>[/TABLE]
It all works as expected, but I just think its very long winded.
Any easier option, (VBA included?) would be good to see and decipher.
Thanks in advance.