MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
How to Get a Value in a Cell to Follow Changes in Adjacent Columns
The content of this file is all about recipes and cooking, hopefully of interest to all who view this post. If anyone is interested in receiving any recipe seen I would be happy to share. If there is interest I can supply a listing of all recipes per any specific folder of your choosing. In addition to Tab #2 there are an additional 20 tabs containing recipes from the following types:
Bread-Muffin Recipes, Breakfast Recipes, Casserole Recipes, Chili-Soup-Stew Recipes, Conversion & How To, Dessert Recipes, Drinks, Egg Recipes, Health, Meat Recipes, Mexican Recipes, Misc Recipes, Oats-Rice-Potato Recipes, Pancake-Waffle Recipes, Peanut Butter, Pizza & Pasta Recipes, Salad Recipes, Sandwich Recipes, Sauce-Spread-Dip-Stuffing Recipes, and Vegetable Recipes
I am working on an xlsm file that Power Queries folder content of documents, PDFs, and Miscellaneous files, and links.
In column A is a file name, column G equals column A, and column H has the header of “Favorites?”, as in which recipes are favorites.
When new files are added in the relevant folder and the power query is refreshed, then any cell marked “Yes” does not follow the recipe name for which the “Yes” was entered in the first place.
What I would like to have happened is when new data enters the power query that any row that contained “Yes” in the H column prior to a refresh follows the recipe name to the new row.
I will provide 2 XL2bb Mini Sheets, the first showing a valid status regarding a specific recipe name coupled with a “Yes” value denoting the recipe is a favorite and the second showing what happens when a new document is added to the folder and power query is refreshed.
I have tinkered with this for the better part of half a day and can’t seem to hit on anything that remotely resembles what it takes to make it work. Note: the 2 instances of Yes have shifted to different recipe names. The first XL2bb Mini Sheet shows Yes for Spicy Hash Browns... and Yes for The best breakfast...; while the second XL2bb Mini Sheet shows Yes for Southwestern Hash with Eggs and Spicy Hash Browns...
Any help would be much appreciated.
The content of this file is all about recipes and cooking, hopefully of interest to all who view this post. If anyone is interested in receiving any recipe seen I would be happy to share. If there is interest I can supply a listing of all recipes per any specific folder of your choosing. In addition to Tab #2 there are an additional 20 tabs containing recipes from the following types:
Bread-Muffin Recipes, Breakfast Recipes, Casserole Recipes, Chili-Soup-Stew Recipes, Conversion & How To, Dessert Recipes, Drinks, Egg Recipes, Health, Meat Recipes, Mexican Recipes, Misc Recipes, Oats-Rice-Potato Recipes, Pancake-Waffle Recipes, Peanut Butter, Pizza & Pasta Recipes, Salad Recipes, Sandwich Recipes, Sauce-Spread-Dip-Stuffing Recipes, and Vegetable Recipes
I am working on an xlsm file that Power Queries folder content of documents, PDFs, and Miscellaneous files, and links.
In column A is a file name, column G equals column A, and column H has the header of “Favorites?”, as in which recipes are favorites.
When new files are added in the relevant folder and the power query is refreshed, then any cell marked “Yes” does not follow the recipe name for which the “Yes” was entered in the first place.
What I would like to have happened is when new data enters the power query that any row that contained “Yes” in the H column prior to a refresh follows the recipe name to the new row.
I will provide 2 XL2bb Mini Sheets, the first showing a valid status regarding a specific recipe name coupled with a “Yes” value denoting the recipe is a favorite and the second showing what happens when a new document is added to the folder and power query is refreshed.
I have tinkered with this for the better part of half a day and can’t seem to hit on anything that remotely resembles what it takes to make it work. Note: the 2 instances of Yes have shifted to different recipe names. The first XL2bb Mini Sheet shows Yes for Spicy Hash Browns... and Yes for The best breakfast...; while the second XL2bb Mini Sheet shows Yes for Southwestern Hash with Eggs and Spicy Hash Browns...
Any help would be much appreciated.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =COUNT(INDIRECT("H5:"&E3)) |
| B1 | B1 | =LEFT(C5,LEN(C5)-5) |
| A2 | A2 | =SUMPRODUCT(--NOT(ISNUMBER(INDIRECT("H5:"&E3)))) |
| B2 | B2 | =MID(B1,1,43) |
| J1 | J1 | =SUMPRODUCT(MAX((I:I<>"")*ROW(I:I))) |
| A3 | A3 | =SUM(A1:A2) |
| B3 | B3 | =RIGHT(B2,LEN(B2)-26)&" -- Plus 3 SubFolders, See D1" |
| E1 | E1 | =COUNT(I:I) |
| E2 | E2 | =MAX(I:I) |
| E3 | E3 | =CELL("address",INDEX(I:I,MATCH(MAX(I:I),I:I,0))) |
| A5:A46 | A5 | =IFERROR(VLOOKUP('pq2'!$A5,'pq2'!$A:$A,COLUMN('pq2'!$A:$A)-COLUMN('pq2'!$A:$A)+1,0),"?") |
| B5:B46 | B5 | =IFERROR(VLOOKUP('pq2'!$B5,'pq2'!$B:$B,COLUMN('pq2'!$B:$B)-COLUMN('pq2'!$B:$B)+1,0),"?") |
| E5:E46 | E5 | =HYPERLINK(D5) |
| F5:F46 | F5 | =IFERROR(VLOOKUP('pq2'!$D5,'pq2'!$D:$D,COLUMN('pq2'!$D:$D)-COLUMN('pq2'!$D:$D)+1,0),"?") |
| G5:G43 | G5 | =B5 |
| I5:I46 | I5 | =A5 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'pq2'!ExternalData_1 | ='pq2'!$A$4:$D$43 | A5:A46 |
| CookingList.xlsm | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | E | F | G | H | I | J | K | L | |||||
| 1 | 39 | F:\Data\Documents\Cooking\Breakfast Recipes\ | 39 | TOC | 46 | |||||||||
| 2 | 39 | F:\Data\Documents\Cooking\Breakfast Recipes | 40 | |||||||||||
| 3 | 78 | Breakfast Recipes -- Plus 3 SubFolders, See D1 | $I$43 | |||||||||||
| 4 | Index | File Name | Hyperlinks | Date Modified | File Name | Favorite? | File Name | Favorite? | ||||||
| 5 | 1 | Amish Breakfast Casserole.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Amish Breakfast Casserole.docx | 01/27/2019 03:04 | Amish Breakfast Casserole.docx | 1 | Amish Breakfast Casserole.docx | |||||||
| 6 | 2 | As-You-Like-It Breakfast Casserole - Copy.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\As-You-Like-It Breakfast Casserole - Copy.docx | 04/22/2018 15:00 | As-You-Like-It Breakfast Casserole - Copy.docx | 2 | As-You-Like-It Breakfast Casserole.docx | |||||||
| 7 | 3 | As-You-Like-It Breakfast Casserole.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\As-You-Like-It Breakfast Casserole.docx | 04/22/2018 15:00 | As-You-Like-It Breakfast Casserole.docx | 3 | Best French Toast.docx | |||||||
| 8 | 4 | Best French Toast.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Best French Toast.docx | 03/17/2017 12:11 | Best French Toast.docx | 4 | Broccoli Rabe and Sausage Scramble.docx | |||||||
| 9 | 5 | Broccoli Rabe and Sausage Scramble.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Broccoli Rabe and Sausage Scramble.docx | 05/05/2013 17:01 | Broccoli Rabe and Sausage Scramble.docx | 5 | Chai Oatmeal.docx | |||||||
| 10 | 6 | Chai Oatmeal.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Chai Oatmeal.docx | 03/03/2012 15:37 | Chai Oatmeal.docx | 6 | Cheesy Tater Tot Breakfast Bake.docx | |||||||
| 11 | 7 | Cheesy Tater Tot Breakfast Bake.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Cheesy Tater Tot Breakfast Bake.docx | 04/22/2018 15:04 | Cheesy Tater Tot Breakfast Bake.docx | 7 | Clean Eating Slow Cooker Overnight Oatmeal.docx | |||||||
| 12 | 8 | Clean Eating Slow Cooker Overnight Oatmeal.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Clean Eating Slow Cooker Overnight Oatmeal.docx | 06/29/2013 12:25 | Clean Eating Slow Cooker Overnight Oatmeal.docx | 8 | Country Style Spanish Potato Omelet.docx | |||||||
| 13 | 9 | Country Style Spanish Potato Omelet.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Country Style Spanish Potato Omelet.docx | 07/13/2020 12:57 | Country Style Spanish Potato Omelet.docx | 9 | Crunchy Breakfast Wrap.docx | |||||||
| 14 | 10 | Crunchy Breakfast Wrap.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Crunchy Breakfast Wrap.docx | 11/03/2018 08:22 | Crunchy Breakfast Wrap.docx | 10 | Easy Breakfast Bake.docx | |||||||
| 15 | 11 | Easy Breakfast Bake.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Easy Breakfast Bake.docx | 03/15/2015 12:31 | Easy Breakfast Bake.docx | 11 | Easy One Pan Breakfast Skillet.docx | |||||||
| 16 | 12 | Easy One Pan Breakfast Skillet.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Easy One Pan Breakfast Skillet.docx | 12/22/2020 23:21 | Easy One Pan Breakfast Skillet.docx | 12 | Egg and Tater Bake.docx | |||||||
| 17 | 13 | Egg and Tater Bake.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Egg and Tater Bake.docx | 03/30/2018 21:49 | Egg and Tater Bake.docx | 13 | Everything Bagel Bombs.docx | |||||||
| 18 | 14 | Everything Bagel Bombs.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Everything Bagel Bombs.docx | 04/22/2018 15:14 | Everything Bagel Bombs.docx | 14 | Grape-Nuts.doc | |||||||
| 19 | 15 | Grape-Nuts.doc | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Grape-Nuts.doc | 02/03/2012 16:33 | Grape-Nuts.doc | 15 | Ham and Cheese Breakfast Bake.docx | |||||||
| 20 | 16 | Ham and Cheese Breakfast Bake.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Ham and Cheese Breakfast Bake.docx | 03/30/2018 21:24 | Ham and Cheese Breakfast Bake.docx | 16 | Potato Omelet Stuffed with Ham and Cheese.docx | |||||||
| 21 | 17 | Potato Omelet Stuffed with Ham and Cheese.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Potato Omelet Stuffed with Ham and Cheese.docx | 02/07/2020 05:17 | Potato Omelet Stuffed with Ham and Cheese.docx | 17 | Sheepherderz Breakfast.docx | |||||||
| 22 | 18 | Sheepherderz Breakfast.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Sheepherderz Breakfast.docx | 02/23/2019 02:10 | Sheepherderz Breakfast.docx | 18 | Shepherdz Inn Breakfast Pie.docx | |||||||
| 23 | 19 | Shepherdz Inn Breakfast Pie.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Shepherdz Inn Breakfast Pie.docx | 02/23/2019 02:09 | Shepherdz Inn Breakfast Pie.docx | 19 | Southwestern Hash with Eggs.docx | |||||||
| 24 | 20 | Southwestern Hash with Eggs.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Southwestern Hash with Eggs.docx | 03/23/2019 22:31 | Southwestern Hash with Eggs.docx | Yes | 20 | Spicy Hash Browns For Breakfast.docx | Yes | |||||
| 25 | 21 | Spicy Hash Browns For Breakfast.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\Spicy Hash Browns For Breakfast.docx | 06/07/2020 23:43 | Spicy Hash Browns For Breakfast.docx | Yes | 21 | The best breakfast for weight loss recipes.docx | Yes | |||||
| 26 | 22 | The best breakfast for weight loss recipes.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\The best breakfast for weight loss recipes.docx | 12/03/2021 09:54 | The best breakfast for weight loss recipes.docx | 22 | The Ultimate Roasted Potato-Egg Breakfast Skillet.docx | |||||||
| 27 | 23 | The Ultimate Roasted Potato-Egg Breakfast Skillet.docx | F:\Data\Documents\Cooking\Breakfast Recipes\Docs\The Ultimate Roasted Potato-Egg Breakfast Skillet.docx | 04/11/2021 13:13 | The Ultimate Roasted Potato-Egg Breakfast Skillet.docx | 23 | Bake-Broil 2.jpg | |||||||
| 28 | 24 | Bake-Broil 2.jpg | F:\Data\Documents\Cooking\Breakfast Recipes\Misc\Bake-Broil 2.jpg | 04/11/2021 13:11 | Bake-Broil 2.jpg | 24 | Bake-Broil.jpg | |||||||
| 29 | 25 | Bake-Broil.jpg | F:\Data\Documents\Cooking\Breakfast Recipes\Misc\Bake-Broil.jpg | 04/11/2021 13:07 | Bake-Broil.jpg | 25 | Breakfast Egg Rolls Air Fryer.url | |||||||
| 30 | 26 | Breakfast Egg Rolls Air Fryer.url | F:\Data\Documents\Cooking\Breakfast Recipes\Misc\Breakfast Egg Rolls Air Fryer.url | 02/17/2020 11:58 | Breakfast Egg Rolls Air Fryer.url | 26 | Tasty Spanish potato omelette SANDWICH.url | |||||||
| 31 | 27 | Tasty Spanish potato omelette SANDWICH.url | F:\Data\Documents\Cooking\Breakfast Recipes\Misc\Tasty Spanish potato omelette SANDWICH.url | 01/26/2020 23:47 | Tasty Spanish potato omelette SANDWICH.url | 27 | 5 Ingredient Sausage Hash Brown Casserole.pdf | |||||||
| 32 | 28 | 5 Ingredient Sausage Hash Brown Casserole.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\5 Ingredient Sausage Hash Brown Casserole.pdf | 12/13/2017 04:06 | 5 Ingredient Sausage Hash Brown Casserole.pdf | 28 | Cracked Out Tater Tot Breakfast Casserole.pdf | |||||||
| 33 | 29 | Cracked Out Tater Tot Breakfast Casserole.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Cracked Out Tater Tot Breakfast Casserole.pdf | 12/13/2017 04:05 | Cracked Out Tater Tot Breakfast Casserole.pdf | 29 | Crunchy Breakfast Wrap.pdf | |||||||
| 34 | 30 | Crunchy Breakfast Wrap.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Crunchy Breakfast Wrap.pdf | 11/03/2018 08:22 | Crunchy Breakfast Wrap.pdf | 30 | Eggsquisite Breakfast Casserole.pdf | |||||||
| 35 | 31 | Eggsquisite Breakfast Casserole.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Eggsquisite Breakfast Casserole.pdf | 12/05/2017 04:10 | Eggsquisite Breakfast Casserole.pdf | 31 | Green Chile And Tomato Egg Casserole.pdf | |||||||
| 36 | 32 | Green Chile And Tomato Egg Casserole.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Green Chile And Tomato Egg Casserole.pdf | 12/13/2017 04:08 | Green Chile And Tomato Egg Casserole.pdf | 32 | No-Turn Omelet.pdf | |||||||
| 37 | 33 | No-Turn Omelet.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\No-Turn Omelet.pdf | 12/13/2017 04:00 | No-Turn Omelet.pdf | 33 | One-Pot Spicy Eggs and Potatoes.pdf | |||||||
| 38 | 34 | One-Pot Spicy Eggs and Potatoes.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\One-Pot Spicy Eggs and Potatoes.pdf | 08/25/2016 22:48 | One-Pot Spicy Eggs and Potatoes.pdf | 34 | Overnight Breakfast Casserole.pdf | |||||||
| 39 | 35 | Overnight Breakfast Casserole.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Overnight Breakfast Casserole.pdf | 08/25/2016 11:28 | Overnight Breakfast Casserole.pdf | 35 | OvernightOatmealRecipe.pdf | |||||||
| 40 | 36 | OvernightOatmealRecipe.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\OvernightOatmealRecipe.pdf | 11/04/2016 19:01 | OvernightOatmealRecipe.pdf | 36 | Pumpkin Spice Oatmeal.pdf | |||||||
| 41 | 37 | Pumpkin Spice Oatmeal.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Pumpkin Spice Oatmeal.pdf | 12/13/2017 03:59 | Pumpkin Spice Oatmeal.pdf | 37 | Sausage, Egg and Cheddar Farmer's Breakfast.pdf | |||||||
| 42 | 38 | Sausage, Egg and Cheddar Farmer's Breakfast.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\Sausage, Egg and Cheddar Farmer's Breakfast.pdf | 12/05/2017 04:01 | Sausage, Egg and Cheddar Farmer's Breakfast.pdf | 38 | Slow Cooker Easy Breakfast Casserole.pdf | |||||||
| 43 | 40 | The Ultimate Breakfast Skillet with Roasted Potatoes and Eggs.pdf | F:\Data\Documents\Cooking\Breakfast Recipes\PDFs\The Ultimate Breakfast Skillet with Roasted Potatoes and Eggs.pdf | 07/05/2020 07:29 | The Ultimate Breakfast Skillet with Roasted Potatoes and Eggs.pdf | 40 | The Ultimate Breakfast Skillet with Roasted Potatoes and Eggs.pdf | |||||||
| 44 | ? | ? | ?? | ? | ? | |||||||||
| 45 | ? | ? | ?? | ? | ? | |||||||||
| 46 | ? | ? | ?? | ? | ? | |||||||||
2 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =COUNT(INDIRECT("H5:"&E3)) |
| B1 | B1 | =LEFT(C5,LEN(C5)-5) |
| A2 | A2 | =SUMPRODUCT(--NOT(ISNUMBER(INDIRECT("H5:"&E3)))) |
| B2 | B2 | =MID(B1,1,43) |
| J1 | J1 | =SUMPRODUCT(MAX((I:I<>"")*ROW(I:I))) |
| A3 | A3 | =SUM(A1:A2) |
| B3 | B3 | =RIGHT(B2,LEN(B2)-26)&" -- Plus 3 SubFolders, See D1" |
| E1 | E1 | =COUNT(I:I) |
| E2 | E2 | =MAX(I:I) |
| E3 | E3 | =CELL("address",INDEX(I:I,MATCH(MAX(I:I),I:I,0))) |
| A5:A42 | A5 | =IFERROR(VLOOKUP('pq2'!$A5,'pq2'!$A:$A,COLUMN('pq2'!$A:$A)-COLUMN('pq2'!$A:$A)+1,0),"?") |
| B5:B42 | B5 | =IFERROR(VLOOKUP('pq2'!$B5,'pq2'!$B:$B,COLUMN('pq2'!$B:$B)-COLUMN('pq2'!$B:$B)+1,0),"?") |
| E5:E46 | E5 | =HYPERLINK(D5) |
| F5:F42 | F5 | =IFERROR(VLOOKUP('pq2'!$D5,'pq2'!$D:$D,COLUMN('pq2'!$D:$D)-COLUMN('pq2'!$D:$D)+1,0),"?") |
| G5:G43 | G5 | =B5 |
| A43:A46 | A43 | =IFERROR(VLOOKUP('pq2'!$A44,'pq2'!$A:$A,COLUMN('pq2'!$A:$A)-COLUMN('pq2'!$A:$A)+1,0),"?") |
| B43:B46 | B43 | =IFERROR(VLOOKUP('pq2'!$B44,'pq2'!$B:$B,COLUMN('pq2'!$B:$B)-COLUMN('pq2'!$B:$B)+1,0),"?") |
| F43:F46 | F43 | =IFERROR(VLOOKUP('pq2'!$D44,'pq2'!$D:$D,COLUMN('pq2'!$D:$D)-COLUMN('pq2'!$D:$D)+1,0),"?") |
| I5:I46 | I5 | =A5 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'pq2'!ExternalData_1 | ='pq2'!$A$4:$D$44 | A5:A46 |
