Replies

Ok, I think I fixed it. Thanks for the screenshot, it helped.

kislany

Thank you all for the replies. John’s solution is the one i will be using, it is perfect for my needs!

Cheers,
kislany

Hi Steve, the reason why this won’t work, is because the boss wants a master that we can use for any other presentations in the future, and that should be with this formatting. I might not work on each presentation myself, so they need a master to use in the company that adhers (sp?) to this particular formatting…

Thanks,
kislany

I tried your file but it’s not working for me. I am not sure why there is the ‘if’ in the formula for the row 2 to be empty. It is actually not empty and I have to add other things to those headings in row to as well – and this is where the formula goes wrong. Can you please have a look?

Thanks for your help as usual

I’ve tried to work through the formula, but I keep getting 0 instead of the actual company name, can you please have a look at at the attached Excel file for the match formula? T he file is a small version of the report I’m working on (with altered information, but the rows and columns layout is the same).
Thanks!

I can’t do the first formula the way you mentioned, because I actually have a few other columns in between with other numbers (which I don’t want to be calculated), but I’ve shows this basic example as illustration. So unfortunately I have to cherry-pick the cells that I’m using for the min.
How would the second function work through my example, when the range is split, I tried it but I got an error at the match part.
Thanks,
kislany

Thanks a lot! I don’t want to use a stationery for each of my emails, only for a few selective, so I need to add it in the actual email, thus there’s no danger of putting the stationery as my default for every outgoing email. I just merely need to put a picture as my ‘watermark’, background on the page. Currently there is no easy solution to my problem, right?

Thanks,
kislany

Thanks for your replies and quick help. I will go through each and see which is mostly suitable for what I need. I need to test a few case scenarios first.

Cheers,
kislany

Hans, this is great as well. Although I will be using for this project Steve’s idea, I already have a use for your macro, as I have a project where I have to dynamically add data to the chart with multiple elements. I will only need to modify it a little bit, but it should work Wow, I haven’t even though I can use this to my other project, but it will work!

Thanks again, you are all great!!
Kislany

Wow, that’s great, thanks a lot Steve, this is just what I need! It’ll take some time to do it, but all I need to do is create the name ranges and change the series in the chartsa. I’ve done a small testing and it works!

Thanks everybody else for helping me, I will go with Steve’s tip, as this requires the least extra work from the users (creating new tables, or adding VBA). I will modify the original sheets myself, and the users won’t even know that anything has been done to it.
Kislany

Thanks Hans for the example, it is definitely in the right direction. I have tried to modify it a little bit as my spreadsheet is structured somewhat differently (see Sheet 3), but my results were less than desireable. Can you please have a look at the macro. I’ve used the data in Sheet 3 and the chart in Sheet 1, so I can take care of the linking problem as well. Basically the macro stops at the line Do While Cells(lngRow, 2) = 0. Plus I’m afraid that once the macro will work, it will populate the chart with wrong series (in rows, rather than in columns), but I could not test that as the chart is not updated. And finally, another question, if I have data in multiple columns, how can I add all the data (each in a different series) to my chart?

Thanks again,

Kislany

Thanks a lot for your quick help. It is a good example, except that it would complicate managing the data, because of two main things:
the table that is used to create the chart is already a huge one, and it is linked from a variety of sources. Actually there are 3 big tables in the sheet, each used for a separate chart. The second issue (should we still go in this direction, which we might if nothing else comes up) is that the week names are text, and not number (ex. w41OCT05), so the formula does not work correctly. How can we modify this formula to count 7 up from there?
Thanks again for your help.

Kislany

Edit: Ah ok, I think I found the offset for counting up, I tried this :=OFFSET($C$20,-7,0) and it worked, but that doesn’t seem to help much now, as user cannot input the week number anymore…*sigh*

I’ve come across an interest question that somebody asked me,. but of course, I have no answer to it, I am not even sure it can be done.
There is a table with weeks (ex. week1….week7), out of which a chart is created, whereby the weeks are in the x axis. Every week a new column is added, so next week we have week 8, or week 9, etc. My colleague wants to always have in the chart the latest let’s say 7 weeks displayed, starting from the most recent week added. So when week 10 is added, we want the chart to display week 4 -> week 10, when week 11 is added, then week 5 -> week 11 (so it’s like drop one, add one), etc. Right now she is doing this manually by editing the chart, deleting the first week and adding the last week as the new series. Is there a we could automate this process somehow, with a formula maybe?

I forgot to mention that I have found a ‘tentative’ solution from Erlandsen’s ‘plot the last 12 weeks’, which would work perfect, except that it doesn’t Reason is that the cells that make up the table are all linked from another file, as it’s a very big report. Erlandsen’s chart works when the last row has been populated and you go and add in manually a new row of data, so the dynamic range gets expanded. However my cells are not empty at all, all the weeksof the year have beeen populated in advance with linking from another file, so if we’re in week 10, week 11 is displayed with a 0 (as it’s the link to something else).
Given this added problem, any solutions maybe?

Thanks!
Kislany

Thanks a lot for your tips, I will have to look into them, and see if my manager is happy with any of them – I have a feeling that the regular color picker is not what he wants, as he wants to select from a limited number of colors when he clicks on the drop-down, but let’s see…

Thanks again,
Kislany

That did help, thanks a bunch!

Kislany