Hi, I have a lookup table in that I am using this function to look for values: -
=IF( HC_Home_T =$J$2 , HC_Away_T =$J$11 , HC_Opp)
Where HC_Home_T, HC_Away_T and HC_Opp are defined ranges.
Cell references $J$2 and $J$11 are cells where I search the table for the team numbers. This formula returns the correct value at for the row that contains the correct two teams. However, below this value are 10 more lines of data I want to retrieve but the team numbers only appear the once.
How do I pick up the 10 cells below HC_Opp and reflect them below this formula?
Thanks
Sorry. I can’t create a solution based on that text alone. At least I can’t do it without wasting a lot of time.
Why don’t you simply attach your sample file and show a hand-made version of what you want to get?
(Named ranges are sometimes useful. Trying to understand what the author of a sheet had in mind they are rarely helpful.)
1 Like
Thanks for the response. I have place the database in DropBox here: -
Sheet 16 is list of possible team cup matches. You will see team 1 & 2, 1 & 3 etc.
Sheet TC_H_Cap is where I am trying to automate a handicap algorithm. At cells J2 and J11 I input the team league positions and the cells then populate at A2 and A11 with the relevant team names. Then, at cell B6 I am looking for where the two team sit on sheet 16 and returning the number in column C. At the moment the cells below B6 on TC_H_Cap are just pulled down to fill. This works with this particular combination of teams but as soon as you change the team numbers for new ones the cell references do not change. I am looking for a way to call up all of the data for each possible match from column C on sheet 16 and replication it in column B on TC_H_Cap.
At the moment there are hidden columns from C to G trying to do this in a purely logical manner but it gets very complex when the top and bottom 2 teams are involved (because the handicap is worked out on form against teams in the vicinity of the opponents) hence trying to use a look-up table.
Hope that is clear, thanks.
(Be sure to understand that my intentions are not at all offensive. I just am not a native speaker of any English, and may express myself badly.)
Sorry. There was a misunderstanding.
I didn’t want to step in and to work on what you call your database (actually a Calc document). My suggestion was about a sample also showing the expected results made by you for the purpose of clarifying your problem and allowing me (and other contributors) to consider possible solutions.
Such a specialised .ods document wouldn’t exceed the maximum size of an upload to this site.
And: I wouldn’t register to any dropbox just to get your “file under development” as is.
Not only answering questions here should be a serious task consuming some time. Adequate cooperation by the questioner is indispensable.
Hi, thanks for the further response. Happy to make quick example file but, being new to this forum, I don’t know how to attach to the post.
When writing a comment, the seventh icon above the editing window (with arrow upwards) is for uploads.
Thanks. In the attached file I have put “IF” statements at sheet 2, B2 and C2. They look for the numbers in cells A2 and A11 on sheet 1 and return the corresponding figures in sheet 1 cells C13 and D13 for the given example in the two yellow highlighted cells.
I want to also return the values from Sheet 1 C14:C17, D14:D17 to sheet 2 at cells B3:B6 and C3:C6 and C19:C23, D19:D23 to sheet 2 at cells B11:B15 and D11:D15. It would then look like sheet 2 columns D & E that I have manually populated.
Further to this, if I then changed the numbers in the yellow cells to 3 and 7 I would want the corresponding data to appear.
Hope that is clear, thanks again Neil
Untitled 1.ods (11.2 KB)
Sugestão de solução com células extras.
As células com fundo laranja, foram alteradas as formulas.
As células com texto em vermelho, são as extras, para deixa-las ocultas, formate o texto na cor de fundo da célula (feito em A3:A12), ou use o formato ; ( ponto e virgula, feito em A14:A23).
Suggested solution with extra cells.
The cells with an orange background, the formulas have been changed.
The cells with text in red are the extra ones, to leave them hidden, format the text in the cell’s background color (done in A3:A12), or use the format ; (semicolon, done in A14:A23).
Untitled 1_GS.ods (19,5,KB)
Hi, thanks for that. I can see that your solution works. However, it would involve quite a lot of work to implement because the real sheet has 256 entries that I would have to insert the decimal numbering for.
@schiavinatto :
Obviously you understood the intentions of the questioner. I did not.
Can you tell me in plain English what he wanted?
Please help me specifically to understand
- for what reason Sheet1 in column A contains either blank cells or the value 3.
- if we can assume equidistant occurrences of the value 3 as assured.
- if we can take as assured that a number occurring in column B also indicates the value 3 in the same row of column A.
- What conclusions you drew from the formulas the questioner had posted.
- In specific what the Boolean expressions in the THEN positions of IF() formulas were supposed to be good for.
Hi @Lupp
I understand that Sheet1 was filled out, it is the data.
In Sheet2 he mentioned that changing the values (column A) did not change the results…
His formula: =IF( $Sheet1.A1:$Sheet1.A35 =$A$2 ; $Sheet1.B1:$Sheet1.B35 =$A$11 ; $Sheet1.C13), not this dynamic, the answer is fixed.
So the solution is with the Vlokup function, but it needs extra data to work correctly.
Hi, @cornishneil, se as entradas estiver dispostas simetricamente, Ă© possĂvel a criação de macro para inserção de linhas necessĂŁria em cada entrada ( no sentida da ultima para a primeira) e a colagem das formulas de decimais.
Caso queira me confiar o arquivo, posso ajudar.
Contato direto: veja aqui…
Hi, @cornishneil, if the entries are arranged symmetrically, it is possible to create a macro to insert the necessary lines in each entry (from the last to the first) and paste the decimal formulas.
If you want to trust me with the file, I can help.
Direct contact: see here…User:Schiavinatto - The Document Foundation Wiki
Hi @schiavinatto, thanks for the offer but I have now populated the table. I didn’t take long with block copy and pasting. The formula is now in and everything appears to be working correctly. Thanks again for pointing me in the right direction.
1 Like