[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

In Excel find the cells above a cell containing data

Posted on 2011-10-04
8
Medium Priority
?
190 Views
Last Modified: 2012-06-27
I have a column which contains some numbers but not in every cell. I want to identify the 2 cells directly above the cells containing data.
0
Comment
Question by:RTsal
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36912592
What do you mean identify? The two cells above every cell or one cell?
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36912600
Can you post a sample of your sheet and how you want it to look? Do you want to calculate with the '2 cells above' values?
0
 

Author Comment

by:RTsal
ID: 36912629
I want to put something in the cells so I can sort/filter the column to show only the rows that have data in that column and the 2 cells directly above the cells with data
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:StephenJR
ID: 36912873
So why not do as gerwinjansen has suggested?
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36913145
>> so I can sort/filter the column to show only the rows that have data in that column
When you filter on the column for rows that have values you will automatically get the '2 cells above' because you say that they also contain data. Could you please explain in more detail or post a sample. I'm afraid we can't help you otherwise. Thx.
0
 

Author Comment

by:RTsal
ID: 36916290
Here is a sample file. I want to add a word to the 2 cells immediately above any cell in the "Missing" column (Col. D) that has a number so that I can search sort and filter on that column and find the rows with the number in it and the 2 cells above them.
missing-sample.xls
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 36916499
In your sample, add this formula to D2:

=IF(SUM(D3:D4)=0,"","Show")       Replace word "Show" with required text.

Add an autofilter to the data and then Apply a "Show Blanks" filter to column D. D2 should still be visible.

Select D2 and all cells below. press Ctrl + D to fill down; those cells with values will not be overwritten because they are hidden.

You can now re-apply the filter to show Non Blanks.

Thanks
Rob H
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 36916569
Alternatively, you can put this formula in E2 and copy down all rows of data without having to worry about the cells that are already populated:

=IF(AND(D2="",SUM(D3:D4)=0),"","Show")

Then filter on column E for Non Blank.

Thanks
Rob H
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question