Update entire row based on one cell value

Hi,

I need to poupulate all cells in a row based one perticular cell value. Example: The attached excel file has 2 sheets. On sheet 1, it has a data set table which will be updated everyday. On sheet 2, I need to update the table based on cell value"YES". If the cell value is "NO" it has to skip to next row which has "Yes" and populate sheet 2. Any help is greatly appreciated.

Thanks

Good nigt.

Try this...

Look file...
Att..

Originally Posted by jaslake

Hi kammariarun

What I understand is, if a line item on sheet 1 gets changed from "No" to "Yes" you'd like it added to sheet 2.

However, if a line item on sheet 1 is changed from "Yes" to "No", what do you wish to happen? Should it be deleted from sheet 2?

Yes. if the value is no it should delete from the sheet 2. I need to display only yes values in sheet 2. Thanks

Originally Posted by jaslake

Hi kammariarun

What I understand is, if a line item on sheet 1 gets changed from "No" to "Yes" you'd like it added to sheet 2.

However, if a line item on sheet 1 is changed from "Yes" to "No", what do you wish to happen? Should it be deleted from sheet 2?

Thank you very much. The formula works best for the provided data set in sheet 1. What if I want to extend the data set. I mean, I will be entering the data everyday basis and I want excel to update the sheet2 data automatically based on sheet 1 input. Thank you very much for your help.

Originally Posted by marreco

Good nigt.

Try this...

Look file...
Att..

Thank you very much. I have a very basic knowledge about VBA programming and Macros. I am having trouble in understanding the program and extend the data for larger set. Thanks for your help.

Originally Posted by davegugg

If you adjust the formulas in my attached sheet, you can put them down to any row and they will still display the correct data. Just put them down to row 1000 or some row you will never reach.

Thank you very much.

Originally Posted by davegugg

If you adjust the formulas in my attached sheet, you can put them down to any row and they will still display the correct data. Just put them down to row 1000 or some row you will never reach.

Can you please mention the use of "-4" in your formula. I am trying to setup your formula for a data where I have more than 15 columns. Thank you very much for your help.

=IF(ISERROR(INDEX(Sheet1!$A$5:$E$16,SMALL(IF(Sheet1!$E$5:$E$16="Yes",ROW(Sheet1!$E$5:$E$16)-4),ROW($A2)-ROW($A$1)),3)),"",INDEX(Sheet1!$A$5:$E$16,SMALL(IF(Sheet1!$E$5:$E$16="Yes",ROW(Sheet1!$E$5:$E$16)-4),ROW($A2)-ROW($A$1)),3))

Originally Posted by davegugg

If you adjust the formulas in my attached sheet, you can put them down to any row and they will still display the correct data. Just put them down to row 1000 or some row you will never reach.

My actual data sheet is atatched. I need to populate the yellow shaded column in sheet2 from the orange shaded data from sheet 1 based on Column P value (Yes or No) in sheet 1.

I know I am asking too much but any help is greately appreciated.

Thanks

Originally Posted by davegugg

You are running into a few different issues:
1 When you have sheet names in your formulas, you need to put them in single quotes.
2 The number you are subtracting is the difference between the row your data starts in and the row you want to return. In this case, you had 17 typed into your formula, but you want 8 because your data in the DPE Operating Data sheet starts on row 9.

=IF(ISERROR(INDEX('DPE Operating Data'!$A$9:$P$100,SMALL(IF('DPE Operating Data'!$P$9:$P$100="Yes",ROW('DPE Operating Data'!$P$9:$P$100)-8),ROW($A2)-ROW($A$1)),1)),"",INDEX('DPE Operating Data'!$A$9:$P$100,SMALL(IF('DPE Operating Data'!$P$9:$P$100="Yes",ROW('DPE Operating Data'!$P$9:$P$100)-8),ROW($A2)-ROW($A$1)),1))

This is an array formula and needs to be confirmed with Ctrl + Shift + Enter.

Thank you very very much. You are awesome.