User Profile
DennisMetro
Brass Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Linking Excel data to Publisher
I copied CTRL+C data from Excel and pasted Home>Paste>Paste Special/Paste Link in Publisher. It came in as a linked table which I can format at will – all borders / outside borders and I can enter the cells and change e.g. font colour. When I repeated this action with data from the same Excel file, a few rows further down and linking to the same Publisher file – one page later - it came in as a linked object which I can format - outside borders but not all (i.e. inner borders) and I can not enter the cells to change the font colour. I can paste using right-mouse but then the table is not linked. Any ideas on why? To the best of my awareness I haven't done anything differently. Thanks for any insights. Dennis3.7KViews0likes0CommentsRe: Excel formula
Sergei, 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. Dennis19KViews0likes1CommentRe: Excel formula
Thank you Sergei. I will try - I am not very advanced with this stuff! I have looked a bit but don't see how it can work as the Getflag is different from my FlagLookups (1 for each team). I was hoping I could do something like this: 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. Dennis19KViews0likes3CommentsExcel formula
1. =IF((C10="")*(D10=""),"",IF(C10>D10,$B$10,$E$10)) this formula correctly fills G16 with either B10 or E10 data. 2. G17 contains a graphic which is linked to a named range and which works fine =INDEX('Linked Picture'!$AA$31:$AA$46, MATCH('Linked Picture'!$G$16,'Linked Picture'!$Z$31:$Z$46,0)) this formula correctly picks a graphic depending on the value of G16 When I select the graphic in G17 the code is =FlagLookup9 I have tried =IF(G16="",""),FlagLookup9 - I want the code to check if G16 is empty and if it is do nothing otherwise select the graphic referred to in FlagLookup9. When I try this I get a message "This formula is missing a named reference or a defined name." Any pointers where I cam going wrong? Thanks.Solved20KViews0likes11CommentsExcel
=INDEX(Sheet1!$Z$34:$Z$49, MATCH(Sheet1!$B$14,Sheet1!$Y$34:$Y$49,0)) What is the correct addition to the above formula to do nothing when Sheet1$B$14 is empty? As the formula stands it matches the value of B14 with the list in Column Y and outputs a graphic from Column Z. When B has a value this is OK but when B is empty I do not want any graphic. Thanks for any help. Dennis674Views0likes0Comments- 2.8KViews0likes0Comments
Re: EXCEL IF FUNCTION
Sorry! I've just now realized that your original solution works! My apologies. One question, if I may. As an old school type merchant - is there a good reference book for this sort of guidance? Years ago I liked to sit at the computer but look up stuff in a manual! I know it's all online these days but sometimes I just get it faster when I'm reading it on paper rather than 2 tabs open! I think it has to do with my age. Thanks again for your help.2.8KViews0likes2CommentsRe: EXCEL IF FUNCTION
Hi Hans, Thanks. The problem is I don't know the values of Y1 and Z1. There is a range of possible values. When I know the values for Y1 and Z1 I could just put them directly into the "______" part of the code. Hence my "whatever the value of this string is".2.8KViews0likes4CommentsEXCEL IF FUNCTION
Hi, So, for example, =IF((H2="")*(J2=""),"",IF(H2>J2,"overload","apples")) this basically says if H2 or J2 are empty then return an empty string but if H2 is greater than J2 then return "overload" otherwise "apples". Fine - ok. Now, if the values "overload" and "apples" are unknown when I write this code how do I get the output to give me "whatever the value of this string is"? Thanks for any help. DennisSolved3.1KViews0likes7CommentsRe: Conditional Formatting - a column
mtarler Thanks a lot. IN the past I have had Col B break up even when the format was set to the entire column. I think that came about when I added additional events and then had to re-sort. In Col C I don't know what the modification does but I don't really need to know. Thanks a million.1.6KViews0likes0CommentsConditional Formatting - a column
Hi. I want to check a column for certain words e.g. cat, dog, mule, chicken, horse and if the searched for word is found then I want to format it a certain way. E.G. cat (dark blue background, white text), dog (beige background, black text, and so on. How can I do that or can I do that. I know I can use conditional formatting and containing specific text option. I find the cell references get muddled over time. Is there a function which will let me look up a format depending on value? Like a sort of if x then (background, font colour and bold or italic), if y then ...... if q then ..... and so on. Do I need some sort of named range or can I not do this? Thanks. Dennis1.7KViews0likes4CommentsRe: Problem with a linked picture in Excel file.
Thanks for your input. For now I am going to assume I don't find a fix and will email to recipients as opposed to making a link to Onedrive or Dropbox for a download. Meanwhile, I'll work on other minor tweaks and let the problem mill around in my mind. Thanks again. Dennis9.9KViews0likes2CommentsRe: Problem with a linked picture in Excel file.
Hi. Using a formula as you suggest brings the data from the table in to the sheet but the formatting doesn't come with it. I could re-do the whole thing with the table on the ist sheet to begin with but I then have to re-do all of the formulae again. 😞9.9KViews0likes4Comments
Recent Blog Articles
No content to show