Forum Discussion
Excel formula
- Jul 05, 2021
You may add named formulas for each cell with flag, e.g. for L17
=XLOOKUP('Linked Picture'!$L$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)and for O17
=XLOOKUP('Linked Picture'!$O$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)
As named formula you need to use any function which returns reference, that could be OFFSET, INDEX or XLOOKUP.
If XLOOKUP is available for you Excel, you may handle empty cells with it. First, add dummy picture at the end of your range with flags (in general it could be in any other place), like
I take blue to make it visible, after testing it could be change on any transparent shape of any form.
Next, add named formula as
=XLOOKUP('Linked Picture'!$O$28, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)
Last parameters indicates what to return if nothing is found. I named it GetFlag. If country exists it gives
If empty cell with country name
Please check in attached file.
input results in R6 and S6
gives me a value in L16 then
in L17 something like
=XLOOKUP(L16,Z31:Z46,AA31:AA46,"",0,)
I know this can lookup the value of L16 and check it against Z31:Z46 but then I want it to insert the matching flag to L16 or return nothing if L16 is empty. I know I can not just stick in AA31:AA46 and the rest as I have done here in my example. Anyway, I will keep thinking about it. Very many thanks for your help. Dennis
- SergeiBaklanJul 06, 2021Diamond Contributor
DennisMetro , you are welcome, glad it helped.
That's bit more practice, try with different variants for better understanding how it works. In general not a rocket science.
- DennisMetroJul 05, 2021Brass ContributorSergei, Thank you very much indeed. That works quite well. I will have to look at it in details to try to understand but I think I know how it ties up with the linked cell. I found that the place holder is best made the same colour as the empty background. Adjusting the transparency of the placeholder, say to 0, means the inserted image when the score is known is also transparent. At any rate you've helped me reach a more satisfying outcome. Although I understand a bit in Excel I always have a feeling that there is a better way or an easier, more logical way that I don't know. For example I still have to work out the underlying facts about linking graphics and the size compared to the cells; trying to make the placeholders a bit smaller in relation to the graphic being linked; and checking to see if when a result is entered and a subsequent game is populated with new teams - the names go in but the format doesn't. I'm guessing that to have the correct format the team names would also have to be treated almost like another set of graphics.
Anyways, thank you again very much for your knowledge and help. Dennis - SergeiBaklanJul 05, 2021Diamond Contributor
You may add named formulas for each cell with flag, e.g. for L17
=XLOOKUP('Linked Picture'!$L$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)and for O17
=XLOOKUP('Linked Picture'!$O$16, 'Linked Picture'!$Z$31:$Z$46, 'Linked Picture'!$AA$31:$AA$46,'Linked Picture'!$AA$47)