Hi All,

First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows:

=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

When the formula is put into the "Data Validation" widget, it returns the "There's a problem with this formula" error.

Normally this would make sense and indicate that there was an error with the formula, but when I put the formula directly into the cell to test it, I can see the results. Although, admittedly, the results return a "#VALUE!". However, when i select the formula in the cell and press F9, it returns the desired result.

I have attached both the workbook (the tab in question is "Expense Input" but it references the tab named "Setup".

Any ideas or help would be greatly appreciated.

First time poster, long time reader. I have been having an issue with a formula I am inputting into the data validation widget to create a dynamic dependant dropdown error. The formula is as follows:

=OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,COUNTA(OFFSET(Setup[[#Headers],[Savings]],1,MATCH([@[Expense Category]],Setup[[#Headers],[Savings]:[Miscellaneous]],0)-1,20,1)),1)

When the formula is put into the "Data Validation" widget, it returns the "There's a problem with this formula" error.

Normally this would make sense and indicate that there was an error with the formula, but when I put the formula directly into the cell to test it, I can see the results. Although, admittedly, the results return a "#VALUE!". However, when i select the formula in the cell and press F9, it returns the desired result.

I have attached both the workbook (the tab in question is "Expense Input" but it references the tab named "Setup".

Any ideas or help would be greatly appreciated.

I'm trying to build a replace formula, but want the data to be pulled from a list that has two columns with 107 rows. The first column would have staff names, and the second would have staff community of residence. Essentially if a staff name is entered in a cell, I want the text to be replaced with the community associated with the staff.

]]>I have a multiple spread sheets that have columns named select your name with a number infront of it.

There is only 1 name per in these columns per row. I need to get all those names into the first column. I'm looking for a way to do this without using =A2&b3&c2 (etc...) reason I don't want to use that formula is because each sheet has this information in different columns and I don't want to have to retype this formula all the time.

If there's a simple way to do this please let me know. attached is an example.

There is only 1 name per in these columns per row. I need to get all those names into the first column. I'm looking for a way to do this without using =A2&b3&c2 (etc...) reason I don't want to use that formula is because each sheet has this information in different columns and I don't want to have to retype this formula all the time.

If there's a simple way to do this please let me know. attached is an example.

I am trying to drag a formula through a column without the cell range overlapping. Actually the cell range is a merged cell on another sheet. Here is the formula: =IF(COUNTBLANK('Sheet1'!F4:F6)=3, "","Done"). The formula itself works as intended but I want to drag the formula through the column, but the next row will have a range of F5:7 rather than F7:F9.

Since I merged 3 rows I the range needs to be F4:F6, then F7:F9, F10:F12, etc., but if I drag the function it goes F5:F7, F6:F8, F7:F9

]]>Since I merged 3 rows I the range needs to be F4:F6, then F7:F9, F10:F12, etc., but if I drag the function it goes F5:F7, F6:F8, F7:F9

My problem is as follows: I have the below schedule for a series of games between 4 teams. For these games, TeamX is allowed to scout any games of their next opponent prior to the match-up with TeamX's match-up with said opponent. So for example, AvsB is Game 1, CvsD is Game 2, AvsC is Game 3. A would be able to scout Game2 because Team C is their next opponent at the time of Game 2. How I determine who a next teams opponent is formulaically, then determine which games of that opponent they can scout?

I.e., In column for, I would like to formulaically "A" in the empty column for Game # 4 & 6, because Team D is Team A's next opponent at the time Team D plays those games. The fourth column is a helper column on the assumption it is easier to match to a martial match-up given there are are two columns a team could be found in.

]]>I.e., In column for, I would like to formulaically "A" in the empty column for Game # 4 & 6, because Team D is Team A's next opponent at the time Team D plays those games. The fourth column is a helper column on the assumption it is easier to match to a martial match-up given there are are two columns a team could be found in.

Game # | Away | Home | ||

1 | A | B | A-B | |

2 | C | D | C-D | |

3 | A | C | A-C | |

4 | B | D | B-D | |

5 | B | C | B-C | |

6 | C | D | C-D | |

7 | A | B | A-B | |

8 | A | D | A-D | |

9 | B | C | B-C | |

10 | A | D | A-D | |

Need help

in table k1:s10 find exact match of a1:g1 and return number of the row where it found to h1

sheet attached

thank you

in table k1:s10 find exact match of a1:g1 and return number of the row where it found to h1

sheet attached

thank you