Need help with multiple IFs... or is there a better way?

[wampyra]

Hello all!

I've been using this forum for couple months now and learned a lot from it [i had no idea about excel at first]... But I didn't find this specific topic... Hope I'm not double posting...

I have a data that i need to transfer to another sheet. The thing is that it has couple rows and columns and the goal is that there are no empty ones in between them when transferring. There is a conditional statement ">0" so if it's 0 skip to the next one.

How to continue from the one that was transferred? I was using IF since that was the one I know how to handle... Except that it's getting to a point where formula length is reaching Mars.

Any ideas?

[Marcol]

I am not clear as to what you are trying to do.

See if this workbook gets you started.

[Pete_UK]

There are 3 basic formulae in the attached workbook to enable you to do this. Firstly, I have set up a helper column with this formula in L7:

Please Login or Register  to view this content.

This is copied down beyond your data (to allow for extra entries in future - the hyphens indicate how far down it has been copied) and it identifies the records that you want to copy elsewhere and establishes the correct sequence.

Secondly, this formula in C19:

Please Login or Register  to view this content.

gives the relative row numbers where those sequences occur. Then this third formula in D19:

Please Login or Register  to view this content.

brings the relevant data from column D of your table. This formula can be copied across into columns E to J in order to bring the corresponding data from those columns. Finally, all those formulae on row 19 can be copied down to complete the table, all bunched up. See what happens if you set the total for the bar in J10 to zero.

Hope this helps.

Pete

[wampyra]

YES! YES! Thank you!

I'm having a problem with keyboard atm [disconnects for some reason], so i'll have to try it out tomorrow when i get a new one.

But this is exactly what I wanted to achieve. Thank you for the notes as well.. I'll need to learn more code definitely.

You saved me.



EDIT: Just applied it [roomies keyboard works well ]
It works perfectly

Both of you got 2 positive votes.
Thank you again for the fast reply