IF Formula Looking at a Range of Cells to Figure Out Correct Outcome by Shifting Rows

Good evening,

I have been watching these posts and picked up a lot of useful tips from this community. I recently got a promotion on April 1st and I am trying to leverage technology to help automate some tasks for my guys to get them out of work earlier.

I have this organizational loading sheet and I am trying to have it return certain information about an order, based off of the shipment number.

The formula I am trying to figure out would be in cell E13.

I want the formula to look at a range of cells (E4-E10). If there is nothing there, I want it to return a value that equals a cell in another tab of the same worksheet called ORDERS!H4.

If it finds something in E4 and not E5, I want it to return the value in cell ORDERS!H5. If it finds something in E4 and E5, I want it to return the value in cell ORDERS!H6...so on and so forth.

I am probably missing something really simple, just frustrated because the project was going well and then I got stuck on this part.

Thanks!

I’m not an excel expert, but (assuming what you mean is that if a cell within the range E4-E10 has something in it, the cell(s) above it within the range will also have something in it), couldn’t you just use: =OFFSET(ORDERS!$H$4,COUNTA(E4:E10),)?

I used your example and it ended up counting the blank spots and shifting the value in the 'H' column accordingly. So it sort of worked in the opposite direction of how I wanted it, perhaps I was explaining it poorly.

Since E4:E10 was blank, I wanted it to return ORDERS!H4. If there was something from E4 to E9, for example, I wanted it to return ORDERS!H10.

Any clearer?

I’m not sure why the formula didn’t work for you, as it produces every result you mention for me. Here it is in excel, if that’s helpful.

After downloading your file, I realized I made an omission...completely my mistake.

The E4:E10 range all return blank values but the cells themselves have formulas that determine that. Once I deleted the formulas in E4:E10, it worked exactly as you had indicated excel-help.

The problem is that I need to keep the formulas in those cells. How do I re-write the formula in E13 to accomplish this? So instead of looking to see if the contents/formulas of the cell are completely blank, it needs to look to see if the results of the formula return something blank or not.

Based on what you’re describing, I would think either of these should work for you (based on whether or not cells E4-E10 are truly blank or simply contain a formula that results in ""):
=OFFSET(ORDERS!$H$4,SUMPRODUCT(--(LEN(E4:E10) > 0)),)
=OFFSET(ORDERS!$H$4,ROWS(E4:E10)-COUNTBLANK(E4:E10),)

Got it working, thanks so much for your help!!