Need formula modification help

[sergiy123]

Hi, everybody. I found formula l needed to automate copy/paste of last 10 rows in data list to new location. The tread im referencing to thread68-703675. The formula, im speaking about is =OFFSET(Sheet1!A1,MAX((NOT(ISBLANK(Sheet1!A1:A1000)))*ROW(Sheet1!A1:A1000))-10,,10,4). This formula have been sudjested by Ken, and it works fine, assuming that data list has no empty sells. If however it has empty sells[entries], value of '0' will be posted to the new location, that is not desirable and in my particular case not asseptable.
If anyone would be able to modify the formula to paste empty sells as empty sells instead of zeros, they would make my day. Thank you wery much for reading this. Sergiy

 

[SkipVought]

Are you referring to cells?

Also 0 in a cell is not an empty cell! And if the cell contains a formula that returns a value that appears to be "empty", it will appear as a zero in other references when using a formula.

The more relevant issue is why do you have a formula in a list that is returning EMPTY???

 

[sergiy123]

Data list unfortunately can have empty cells. Yes lm referencing to the sells.Some of these can be empty. The cells lm referencing to can have empty entries, where value is unknown or not present. It makes target sells display value of zero, that unfortunately is value by itself in posterior calculations. I would prefere the formula to display empty cell if referenced cell is empty also. Original cells lm referencing to have just numbers or are empty. Hope it clarify situation.

 

[SkipVought]

I'd use a query where the criteria can be simply stated.

However since your table structure is unknown, it would be difficult to venture a specific solution.

 

[sergiy123]

Hi. There is no table, just data list. Data presented in form of numbers,where column A has no empty cells, but columns b,c,d can have some cells empty. Column A without empty cells permits to the formula to operate as intended. Empty entries in other columns are posted to the new location as zero.I need them to be posted as empty cell.

 

[SkipVought]

"There is no table, just data list."

Sorry, what you have is a table. You may think of it as a list, but it is a table.

"Data presented in form of numbers,where column A has no empty cells, but columns b,c,d can have some cells empty."

Well, now you enter some new criteria! All along your description strongly implied a single column of data, (what I would call a List) and nothing else. THAT is the structure that I was hoping that you would declare.

So again, I ask you to define the structure of your table and post a sample of you table that demonstrates your dilemma.

 

[SkipVought]

Let me explain further.

A List strongly implies DATA, not the absence of data, which is what you apparently have. It you're missing data, then you have an incomplete list.

 

[sergiy123]

Ok l will post data table tonight. Thank you wery much for your explanation. Im relatively new to excel and not familiar enough with all details of its terms. Terribly sorry for confusion. And one more time very big thank you.

 

[madonnac]

The easiest way is with an IFBLANK test.

=IF(ISBLANK(YourCellFormula),"",YourCellFormula)