Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking at multiple cells
Hello,
I would like to reference a cell for data, however if that cell is blenk then look at the next cell, etc. For example: In P1 I would like to reference cell F1, but if E1 is blank then the formula looks at D1, and if D1 is blank I would like to look at C1, if C1 is blank I would like to look at B1, and if B1 is blank I would like to populate P1 with the data from A1. A1 will always have data, but the other cells may be blank. Thanks in advance for the help. Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking at multiple cells
If your A1 to F1 data is Numbers then use the below formula. Copy and paste
the below formula in P1 cell. =LOOKUP(10^10,A1:F1,A1:F1) If your A1 to F1 data is BOTH NUMBERS AND TEXTS OR TEXT then use the below formula. Copy and paste the below formula in P1 cell. =INDEX(A1:F1,MAX(COLUMN(A1:F1)*(A1:F1<""))) It is an array formula so it requires Cntrl+Shift+Enter. After pasting the above formula in P1 cell place the cursor in P1 cell and press F2 and hit Cntrl+Shift+Enter. Now in formula bar the formula will be surrounded by the curly braces after hitting the Cntrl+Shift+Enter. General enter wont work. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jim" wrote: Hello, I would like to reference a cell for data, however if that cell is blenk then look at the next cell, etc. For example: In P1 I would like to reference cell F1, but if E1 is blank then the formula looks at D1, and if D1 is blank I would like to look at C1, if C1 is blank I would like to look at B1, and if B1 is blank I would like to populate P1 with the data from A1. A1 will always have data, but the other cells may be blank. Thanks in advance for the help. Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking at multiple cells
Perfect - thanks!!!
"Ms-Exl-Learner" wrote: If your A1 to F1 data is Numbers then use the below formula. Copy and paste the below formula in P1 cell. =LOOKUP(10^10,A1:F1,A1:F1) If your A1 to F1 data is BOTH NUMBERS AND TEXTS OR TEXT then use the below formula. Copy and paste the below formula in P1 cell. =INDEX(A1:F1,MAX(COLUMN(A1:F1)*(A1:F1<""))) It is an array formula so it requires Cntrl+Shift+Enter. After pasting the above formula in P1 cell place the cursor in P1 cell and press F2 and hit Cntrl+Shift+Enter. Now in formula bar the formula will be surrounded by the curly braces after hitting the Cntrl+Shift+Enter. General enter wont work. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jim" wrote: Hello, I would like to reference a cell for data, however if that cell is blenk then look at the next cell, etc. For example: In P1 I would like to reference cell F1, but if E1 is blank then the formula looks at D1, and if D1 is blank I would like to look at C1, if C1 is blank I would like to look at B1, and if B1 is blank I would like to populate P1 with the data from A1. A1 will always have data, but the other cells may be blank. Thanks in advance for the help. Jim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking at multiple cells
You are welcome and thanks for feeding back.
-------------------- (Ms-Exl-Learner) -------------------- "Jim" wrote: Perfect - thanks!!! "Ms-Exl-Learner" wrote: If your A1 to F1 data is Numbers then use the below formula. Copy and paste the below formula in P1 cell. =LOOKUP(10^10,A1:F1,A1:F1) If your A1 to F1 data is BOTH NUMBERS AND TEXTS OR TEXT then use the below formula. Copy and paste the below formula in P1 cell. =INDEX(A1:F1,MAX(COLUMN(A1:F1)*(A1:F1<""))) It is an array formula so it requires Cntrl+Shift+Enter. After pasting the above formula in P1 cell place the cursor in P1 cell and press F2 and hit Cntrl+Shift+Enter. Now in formula bar the formula will be surrounded by the curly braces after hitting the Cntrl+Shift+Enter. General enter wont work. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jim" wrote: Hello, I would like to reference a cell for data, however if that cell is blenk then look at the next cell, etc. For example: In P1 I would like to reference cell F1, but if E1 is blank then the formula looks at D1, and if D1 is blank I would like to look at C1, if C1 is blank I would like to look at B1, and if B1 is blank I would like to populate P1 with the data from A1. A1 will always have data, but the other cells may be blank. Thanks in advance for the help. Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select multiple adjacent cells of multiple cells without selecting | New Users to Excel | |||
Select multiple adjacent cells of multiple cells without selecting | Excel Worksheet Functions | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
Date and time stamping multiple cells for multiple entries. | Excel Worksheet Functions | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions |