Hi All,
The target is column B. I have 2 issues:
1. Where the value is "0", I need to pull the value from the cell that is right 1 and down 1. I have done the first 3 manually.
2. I need to be able to drag this formula down Column B without changing the other values that are not "0".
I have tried a combination of IF and OFFSET formulas but still can't get it right.
PLEASE HELP!
Thank you in advance.
Excel Question.jpg
Welcome to the forum!
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Use this in C2, copied down:
=IF(A2="Configurable","",IF(B2="0",LEFT(B3,SEARCH("-",B3)-1),LEFT(B2,SEARCH("-",B2)-1)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
A little modification of Gleen formula:
Formula:
Please Login or Register to view this content.
why? because search("-",B2) =#value if there is no "-"
but maybe I'm wrong
Last edited by sandy666; 11-16-2016 at 05:20 AM.
Sandy, Unfortunately, you are wrong. If you check it, you will seen that mine returned the desired result, whereas yours did not...
Last edited by Glenn Kennedy; 11-16-2016 at 05:49 AM.
Ok, Gleen
In case if cell next to simple has "-" it works
I (bet) assumed parent = 6 (from picture) and source has "-" or not.
It is never late to learn :D
Formula:Please Login or Register to view this content.
Last edited by sandy666; 11-16-2016 at 07:06 AM.
Hi everyone, Thanks again for your help.
I plugged in the formula suggested but unfortunately have not received the desired response.
As per the moderators advice, I have attached a sample excel workbook. With the excel attached, it should be easier.
Let me try to explain what I am looking to do:
The desired outcome is to replace the highlighted blanks(ex. B14,B21,B34,B36) with the respective value shown in red font (ex. B2, B6, B12, etc).
The formula must be dragged down the entire column without changing any of the other "simple" values (ex. B3,B4,B5)
Background
Maybe a little context will help. The configurable SKU is considered the parent. For example "78260" in column B is the parent sku. The simple SKUs in Column B are child variations of the parent. For example "78260-10" represents style 78260 in a size 10. "78260-11" represents style 78260 in a size 11 etc.
Now you really have me confused. Please amend to show EXACTLY what your expected results are.
Try like this in a couple manual steps...
First remove the 0s
Use Find/Replace on column B to remove the 0's and replace them with nothing.
Make sure to check "Match entire cell contents" in the Find/Replace options.
Now with those 0's removed, and those cells blank
Highlight column B
Press CTRL + G
Click Special
Select Blanks
Click OK
Press =
Press Right Arrow
Press Down Arrow
Press CTRL + ENTER
haha oh no! I have attached the workbook again. This time I have included the data as it currently is ("Current") and how I want it to look like ("Goal").
Jonmo1, your idea sounds promising, i'm going to try it now!
Given the new example, you're talking about blanks, not zeros.
So you can skip the find/replace part...
Jonmo1, omg it works! Thank you so much! On my first full day on this forum, I already see its tremendous value. I will try to pay it forward!
Thanks guys!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks