IF formula but ignore blank and move to next criteria

[jilp100]

Hi all,

Any help would be appreciated. I have a spreadsheet, and I want to take from the main tab and if it meets the criteria take 3 columns to the left.

So, main sheet has some information on it. If that person is a male, then take all columns to the left, and copy this into the tab for males. And vice versa for female.
The issue is, it leaves a gap each line down as it searches through the main sheet, if I use=IF('Main sheet'!$E7="yes",'Main sheet'!B7:D7) or false or blanks.
I don't know if you can make it, look at the data, check it if it meets the criteria, if it doesn't, moves to next line, but this information in male and female tabs doesn't leave blanks each row it doesn't match.


thanks for any help

J

[AliGW]

Here is a very simple two-query PowerQuery solution:

M Code (male):

Please Login or Register  to view this content.

M Code (female):

Please Login or Register  to view this content.

[jilp100]

Hi,
Thank you for your super fast reply. I probably should have explained a little more.
the male and female tab will have further information manually added each time. So it takes from the main tab, the majority of the information but then someone manually adds some extra column information on each one. So filtering out isn't a option I don't think.
Any ideas?
Sorry, I should have been clearer.
Thanks
J

[AliGW]

It's perfectly possible to add the extra columns you need within PowerQuery, so yes, it can be done with a little foresight - this covers it: https://exceleratorbi.com.au/self-re...s-power-query/

[jilp100]

Thanks, is there a way to auto refresh? I do you have to manually do it each time?

[AliGW]

You can write a worksheet change event to detect when the master table is updated:

Please Login or Register  to view this content.

Your range would need to cover the area that the master table might grow to cover (in other words, add as many rows as you think you'll need).