Excel Formula to pull data from another sheet and place cell blank if value is 0

Hi All,

Need an excel formula for this requirement..

I am pulling values from another sheet to current sheet..

For Example:

"Sheet1" is my current sheet and "Sheet2" is the sheet from which I need to pull the values.

The values in sheet 2 will be in column A,C,E,G etc [alternate cells]. The Sum of these cells should be placed in Column A of "Sheet1"

So the requirement is as below:

1. I need to check if there any value exists in alternate cells of "Sheet2". If no value exists in any of alternate cells, then leave column A of Sheet1 blank.
2. If alternate cells of Sheet2 has values, then take the sum of the values.
a) If the Sum of the value is 0, then then leave column A of Sheet1 blank.
b) If the Sum of the value is greater than 0, then place that value in Column A of Sheet1.

Thank you.

Sample Attached...

Sheet1 is the result what I want and Sheet2 contains the actual values.. I am taking the sum of alternative cells A,C,E,G

Hi,

I could see that, you had put formula in only cell A1 and A4.. but in my case I might/might not have values in other cells.. so based on that it should calculate..

For example.. in my sample file attached.. in Sheet2. 2nd row has values 0.. so it should first get the sum of all these values then it should check if it is 0 or greater than 0.. if 0 dont put anything.. else put the sum of values.. this should be same for all cells... also if the cells in sheet2 are blank then it should not put anything in sheet1

Try this:

=IFERROR(1/(1/SUMPRODUCT(--(MOD(COLUMN(Sheet2!A1:G1),2)),Sheet2!A1:G1)),"")

Hi,

This updated file looks good but still in A2 and A3 i dont want to put anything because if the sum of values are zero then i should show a blank cell and also if there is nothing in sheet2 then also i should show blank cell..

Here for A2, sum of values are zero.. so it should be blank
for A3, nothing is there in Sheet2 to get the sum, so this should also be blank...

In otherwords.. now your formula is placing zero in some cells.. so if it is zero then just show blank cell.. is it possible?

Originally Posted by Teethless mama

Try this:

=IFERROR(1/(1/SUMPRODUCT(--(MOD(COLUMN(Sheet2!A1:G1),2)),Sheet2!A1:G1)),"")

Hi,

I am getting an error if I paster this formula in the cell and click on enter!!

See post #6

Originally Posted by szpt9m

Hi,

I am getting an error if I paster this formula in the cell and click on enter!!

It work fine on my computer

Originally Posted by Teethless mama

It work fine on my computer

can u attach that sample file which worked in your system.. I may be missing something.. I am not that good at Excel Programming!

See the attached file

Thanks.. it is working as expected.. but your formula in post #6 contains comma(,) in place of pipe(|).. thats why it didnt work for me!! Thanks again

You're Welcome!

Oops!!! There is a slight change in the requirement now.. I am really sorry for this but my boss changed it... Can I get average of values instead of sum?

Hi All,

Pls Help me..

I am able to get sum of values as exepected from the below formula

Please Login or Register  to view this content.

can i get average of values instead of SUM?? all other requirements are same.. just need to get average instead of sum!!

is it for average or sum?

i dont knw why.. i am not able to use any formulas given directly here... it is replacing pipe(|) with comma(,) and giving an error...! if possible pls attach the sample file u r using.. and pls give me a formula to get average and not the sum..

it is picking only A,C,E.....etc columns.. if I modify it to get B,D,F...etc columns its not working.. ow can i get B,D,F...etc column values..!

wonderful!! worked as expected... Thanks a lot Ramananhrm