Extracting certain data depending on criteria

Hope there is an easy solution to this.... lol

Have a perfect working spreadsheet, but now need to extract certain bits of data depending on one criteria.

Each row contains data in different columns.

What i need to do is depending on the criteria of a cell (i.e. whether its Y or N) i would like to extract certain cells onto another worksheet all within the smae work book

i.e for example
If i have columns A,B,C,D,E and F and then x amount of rows (as these can change as data is entered) if a Y is entered in F then it extracts the data out of columns A, C and D

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

In Timetable Service sheet, cell R2, enter formula:

=IF(O2="Y",MAX(R$1:$R1)+1,0)

and copy down as far as you want...

Then in DDA sheet, cell G1, enter formula:

=MAX('Timetabled Service'!R:R)

Then in A3, enter formula:

=IF(ROWS($A$1:$A1)>$G$1,"",INDEX('Timetabled Service'!D:D,MATCH(ROWS($A$1:$A1),'Timetabled Service'!$R:$R,0)))

and copy down as far as you want....

Formula can be copied across too, but you will need to adjust the range right after the INDEX() to match the column you want to extract...

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Please post a new sample workbook showing your requirements...

Sorry, thought it may of been easy..

Have post another example workbook, its the same as the lastone but witht he second page added, as you will see the second page is of a disfferent format

Hope this help

And thank your for all your help

G

Your second worksheet is protected so can't test...but

In order to create a single formula that you can copy down and be able to extract from both sheet, it would be advisable to have the same setup in both sheets...

Then...

You would apply the same helper formula in R2 of Timetabled Service sheet...

=IF(O2="Y",MAX(R$1:$R1)+1,0)

copied down.

then in the "fixed" Frequent Service sheet R1, add formula:

=Max('Timetabled Service'!R:R)

and in R2, copied down:

=IF(O2="Y",MAX(R$1:$R1)+1,0)

In G1 of DDA sheet..

=Max('Frequent Service'!R:R)

and in A3:

=IF(ROWS($A$1:$A1)>$G$1,"",IF(ISNUMBER(MATCH(ROWS($A$1:$A1),'Timetabled Service'!$R:$R,0)),INDEX('Timetabled Service'!D:D,MATCH(ROWS($A$1:$A1),'Timetabled Service'!$R:$R,0)),INDEX('Frequent Service'!D:D,MATCH(ROWS($A$1:$A1),'Frequent Service'!$R:$R,0))))

See my reply anyways... you need to rearrange the second sheet to look like the first sheet for a formula that you can copy down and get results from both sheets in proper fields....

... try to apply my formulas and if you can't get it, attach a workbook of where you got to...

My formula is an IF() formula that says to look first, in Sheet 1, then if not there, look in Sheet2.. not pulling to lots and and entering in one cell.

Again, if you setup both sheets to be of the same format, then the formula looks at the first sheet and extracts all relevant info..when all that info is taken out, then it looks in the second sheet and pulls all the relevant data from there...

Maybe, since you can't change the 2nd sheet, change the 1st to match the 2nd and apply the formulas (adjusted as necessary).

Can you post something showing what you mean...

As far as faster... not sure if you can.. I used helper columns to avoid array formulas in order to make it most efficient...

I am not really a coder... so can't help you there.. but you might want to post that question separately.. as it is really a new question and belongs in the programming forum.

And then please mark this thread as Solved.