| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a value based on another value
Thank you - it's perfect.
"Rick Rothstein" wrote: Sorry, I need to make a correction (you are starting from Z2, not Z1). Use either this formula... =IF(A2="","",INDEX(Z:Z,1+SEARCH(A2,"ABCDEHJKLMS")) ) where the addition of the 1 is meant to adjust the offset for index to match its starting point one cell down from Z1 which is where Z:Z starts from), or you can use this formula... =IF(A2="","",INDEX(Z2:Z100,SEARCH(A2,"ABCDEHJKLMS" ))) where you would set the Z100 reference to the last possible cell in Column Z that will hold data (probably Z12 for the data you posted). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... No, you were clear... I just forgot to complete the formula before I posted it. Here is what I had intended to post... =IF(A2="","",INDEX(Z:Z,SEARCH(A2,"ABCDEHJKLMS"))) Sorry for any confusion. -- Rick (MVP - Excel) "AAbrams2008" wrote in message ... I don't think I was too clear. What I'm trying to return is: If the value inserted in A2 is then the value in O2 should be A the value from cell Z2 B the value from cell Z3 C the value from cell Z4 null 0 Sorry about my lack of clarity "Rick Rothstein" wrote: Put this formula in O2... =IF(A2="","",SEARCH(A2,"ABCDEHJKLMS")) Note: You didn't say what you wanted to happen if A2 was not empty (or, as you say, "null"), but also is not one of the letters you listed... the above formula will generate a #VALUE! error for that condition. -- Rick (MVP - Excel) "AAbrams2008" wrote in message ... I have a place where the value of A2 can be A,B,C,D,E,H,J,K,L,M,S, or null. Based in the value entered in this cell, I want O2 to take the value of Z2,Z3,Z4,Z5,Z6,Z7,Z8,Z9,Z10,Z11,Z12, or 0. I need to calculate this for all row values of the column O. It's too complex for a nested if/then loop, so I think I need to either insert a custom macro or embed a VB function, however both of these are things I don't have a lot of experience with. What do I need to do? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inserting Columns Based on List | Excel Discussion (Misc queries) | |||
| inserting a row based on a condition. | Excel Worksheet Functions | |||
| Inserting row based on critéria using VBA | Excel Programming | |||
| Inserting row based on value in cell in Column C | Excel Programming | |||
| Inserting rows based on another cells value | Excel Programming | |||
Hybrid Mode
