LinkBack URL
About LinkBacks
a1 has a value
c1 has a formula
d1 associated numbers
e1 c1 fomula
f1 c2 formula
g1 c3 formula
example
a1=1 c1="*"&A1&"*" d=1,2,3 e(I want e to populate d1 the 1) f(I want to populate d1 the 2) and g(I want to populate d1 the 3)
a1 has a value
c1 has a formula
d1 associated numbers
e1 c1 fomula
f1 c2 formula
g1 c3 formula
example
a1=1 c1="*"&A1&"*" d=1,2,3 e(I want e to populate d1 the 1) f(I want to populate d1 the 2) and g(I want to populate d1 the 3)
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Sample attached.
If cell D2 is just a concatenation of the values in A2:A4 then try this formula entered in E2 and copied across:
=INDEX($A2:$A4,COLUMNS($E2:E2))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Sadly it's not, d2 is a variable it just so happens 1,2,3 is there for the sample, so I need it to look at the 1,2,3 and if it said 1,2,89 that it would populate 89 value in g2 instead.
As long as there aren't too many numbers in the cell...
Entered in E2 and copied across:
=--TRIM(MID(SUBSTITUTE(","&$D2,",",REPT(" ",255)),COLUMNS($E2:E2)*255,255))
Wow that works the only thing left is I need the formula to be written like this "*"&A2&"*" it's a upc
So I need it to take the d2 number and insert it into this formula "*"&A2&"*" (with A2 being the d2 1,2,3)
Sorry, I don't understand.
If D2 contains: 1,2,3
What result should be in E2?
What result should be in F2?
What result should be in G2?
ah the result in e2 should be "*"&d2(1,2,or3)&"*"
If D2 = 1,2,3 and you want:
E2: *1*
F2: *2*
G2: *3*
This formula entered in E2 and copied across:
="*"&TRIM(MID(SUBSTITUTE(","&$D2,",",REPT(" ",255)),COLUMNS($E2:E2)*255,255))&"*"
Bingo! last issue, so f2 has 2,3
g will show *2*
h will show *3*
I shows **
J shows **
K shows **
Is there a way to blank it out if there is no value, I have tried conditional formatting but that didn't work.
A couple of questions:
Will the cell ever be empty?
If the cell is not empty, will it ALWAYS contain at least 2 numbers separated by a comma? Will there ever be just a sinlge number and no commas?
The cell can be empty, if there are no associated barcodes. And yes there are times it will contain 1 number
so the cell can be empty, 1 number, 2 numbers, 3 numbers, up to 6
Try this...
=SUBSTITUTE("*"&TRIM(MID(SUBSTITUTE(","&$D2,",",
REPT(" ",255)),COLUMNS($E2:E2)*255,255))&"*","**","")
Solved! thank You!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks