Vlookup - display previous value if zero

[MMarius]

Hello,

I have an external connection pointing to an xml which display some values.


Sheet1: Values
Sheet2: External values

I managed to do it with Vlookup but since the XML sometimes doesn't updates correctly and points to "zero" (since it can't read it from external source) I will want to retain the value that was before zero.

E.g.: Let's say in Sheet2 (external xml), in A1 last value was 250150, but after "refresh all", it gave 0 since it can't read, on sheet1 it will display 0, i want it to display 250150 (retain the last value that is different from zero).

 

[DSCfromCFA]

G'day MM,

Try in Sheet 1 cell A1, and any others as required.:
=IF(Sheet2!A1 > 0,Sheet2!A1,A1) - don't include the spaces around the greater than sign as that is a format issue with these posts.

Cheers

shane

 

[MMarius]

G'day MM,

Try in Sheet 1 cell A1, and any others as required.:
=IF(Sheet2!A1 > 0,Sheet2!A1,A1) - don't include the spaces around the greater than sign as that is a format issue with these posts.

Cheers

shane

Hello,

Indeed, on a new created excel file, it works. But mine (based on TABLE), it doesn't work as intended. For Table do i need to apply different formula?

Thank you

 

[DSCfromCFA]

G'day MM,

I put the formula into a simple table and it works just the same. In what way is it not working as intended for you?

shane

 

[MMarius]

G'day MM,

I put the formula into a simple table and it works just the same. In what way is it not working as intended for you?

shane

I attached you the demo, in sheet "TEST1", E7 starts and retrieve data from sheet "MODIFY" but doesn't work as intended.

Thank you

DOWNLOAD FILE

 

[Fluff]

Try
=IF(MODIFY!A2 > 0,MODIFY!A2,A7)

 

[MMarius]

Try
=IF(MODIFY!A2 > 0,MODIFY!A2,A7)

UPDATE: managed to solve it Thank you Can be closed


Hi Fluff,

apprently works now, how do i use the above code with "if blank"?

I have values in D1 & E1 and i want if there are both blanks, to not display the formula.

D1 = empty
E1 = empty
H1 = value from formula not to be displayed =IF(MODIFY!A2 > 0,MODIFY!A2,A7)

If:

D1 = value
or E1 = Value
H1 = value to be displayed based on formula

 

[Fluff]

That formula is in E7 so you cannot have it checking itself.