Posts by Xenoun


    I'm currently writing a macro to add one year to the date in certain cells among other things.

    I'm just stuck on how to do this as usually if you try to include a cell in a formula and have the output in the same cell you get a circular reference. (e.g in A1 type =A1+1)

    Does anyone know how to do what I need?

    I've searched around and have found the general syntax to add a year to a date but not how to do it in one cell.

    Thanks for any help

    Re: Quickly change a named range formula

    Ok, I can see how my explanation could be confusing.

    Basically I have 219 dynamic named ranges that grab data from a specific column which is then used in a chart.

    At present, the formulae for the named ranges are along the lines of:


    I'm modifying all of these to do this instead:


    I already have all of the new formulae typed out in cells and labelled as to which named range they're for, I just need a quicker way to copy/paste the new formula into the appropriate named range. The names of the ranges are in cells A2:HK2, the current formulae are in A3:HK3 and the new formulae are in A4:HK4. I had it this way so that I could verify that the numbers didn't change due to a typo etc when I was modifying the old formulae.

    At present I have to click the cell the formula is typed in which takes my cursor to the end of the third bar of text, hold shift and hit home/left till its all selected, copy, open the name manager scroll down until I find the correct name, select it and overwrite the current formula with paste.

    If I was only doing 20-30 changes then I wouldn't mind but doing it 219 times is a bit time consuming. I was just wondering if there may be a quicker way to do this, there may not be in which case i'll just keep going with it


    I've got a workbook that I've previously constructed to automatically create and update trend charts for 14-15 different categories. I'm now in the process of adding more functionality to the workbook which basically means I'm re-doing the entire thing.

    For the automatic filling and updating of data in the charts i've used dynamic named ranges, ending in a total of around 300 named ranges. Now that extra functionality is required i've re-written 219 of them and expanded on what they did before.

    Currently I need to update those 219 named ranges with the new formulae and I was wondering if there's a quick way to do this. I've just been clicking in the cell that i've written the formula, holding shift and hitting home/left until all of it's selected, copying it and then opening the name manager, finding the named and pasting it in the formula section. It's a very inefficient way to update it especially as I have to do it 219 times so i was wondering if there are any shortcuts to update the named ranges quickly.

    I don't think there would be, but it was worth asking.


    I'm currently trying to write a formula that amongst other things will select the last 40 entires in a certain column.

    I've previously done this for an array of data that increases in columns, and selected the last x rows of data but i'm having issues with my latest formula and seem to be going in circles.

    After reading a lot of similar posts I think my problem is that in the column with the data the top 3 cells are blank, then I have text and the next cell has data. This means my data start in row 6 and continue downwards.

    My data are linked to another sheet and allow up to 400 entries to be recorded, which results in a #N/A error for any blank cells (at present there are about 300 error cells underneath the data).

    Previously i've used a dynamic named range to select all of the data, but I now need to add the functionality of short/long term data. I've sorted out the hardest part already with an if statement amongst other things and this is the last thing that's got me stuck.

    I've tried using COUNTA so far to return the number of values in the column and use that to get me the last 40 entries but for some reason my COUNTA function returns 1 no matter what I do with it.

    =COUNTA("'"&'Combined Body-Fascia Graphs'!$C$55&"'!BW6:BW20")

    I've also tried the MATCH function which returns a #VALUE error even when entered as an array

    =MATCH(1000,"'"&'Combined Body-Fascia Graphs'!$C$55&"'!$BW$6:$BW$20")

    i'm currently revisitng previous formulae i've written which follow similar lines but I haven't found anything yet. Any help would be appreciated. I'd post my main formula but its pretty long and this is the part that breaks down when i evaluate it.

    Re: Converting multiple rows to multiple columns

    The only way I can see to do that is definately a manual operation. The problem is that you dont just want to transpose but also break up certain parts of some of the cells into cells of their own. The parts you want to break up dont always have the same number of text strings so there isnt really any way for a formula or macro to differentiate how many strings of text to put in a cell if you want 3 the first time and then 4 the second.

    Re: Converting multiple rows to multiple columns

    The only way I can see to do this is to just manually transpose each block of three entries.

    It will take a while, but i'm guessing you've already spent a fair bit of time trying to find a solution...may be quicker just to bite the bullet and do the manual operation

    Re: changing cell color of a row based on system date

    You need to use a formula like =$D$3>TODAY()
    If you use that then any date in D3 that is after Today will be filled in the colour that you choose (i.e. Green)

    For the dates that have gone past teir deadline use the formula =$D$3<TODAY()
    This will trigger on any date that is earlier than today.

    If you used =TODAY() then the cell would only be filled when the date in the cell you have the rule applied to matches today's date.

    Also, if the colour doesnt change or go blank when you change the date make sure the calculation option is set to automatic not manual. When I tried out the formula before posting it here the colours weren't changing because my sheet was somehow set to manual
    For Excel 2003: Tools > Options > Calculation tab > Automatic

    Re: changing cell color of a row based on system date

    You can do this with Conditional Formatting

    If you go: Conditional Formatting > New Rule > Use a formula to determine which cells to format

    You can then enter in the box something like: =$D$3>TODAY() then format to fill red etc

    For less than simply change the sign to < and choose another colour, same goes for current date using =

    To only highlight certain rows with this formula simply select those rows or cells first before creating the rule. Alternatively you can click "Manage Rules" afterwards and there's an option to set which cells the rule applies to

    Re: Find/match text from one cell to the same text in a certain row.

    The entire post was relevant for my reply. I do see harm in the extra emphasis, especially if your whole goal is to 'reduce clutter'. You've created an extra post which added no value and then due to this three other posts have now also been created which further increases the clutter and detracts from what this thread was about.

    Personally it annoys me greatly when moderators decide they need to step in all the time just to add their little bit and not actually help the person asking a question.

    What Justin did was the perfect example of how it should be done, he edited it and also posted some advice. Doing it this way points out to me or any other user what the correct procedure is and also adds value to the thread rather than 'increasing clutter'.

    For Justin:
    The workaround method that I went with works well enough. I ended up encountering yet another problem, searched for a solution, started another thread and then figured it out while posting replies to myself in the thread.

    Thanks for your help on this, I was having a lot of trouble trying to combine all of those functions into one working formula.

    Re: Make cell return error if blank

    All good, fixed it with an If statement. Here's the final formula in case anyone was interested:

    =IF(OFFSET(INDIRECT(ADDRESS(6,MATCH(LEFT('Change Tracking'!$D$2,FIND(" ",'Change Tracking'!$D$2,1)-1),'Change Tracking'!$6:$6,FALSE),,,"Change Tracking")),7,1,1,1)= 0,NA(),OFFSET(INDIRECT(ADDRESS(6,MATCH(LEFT('Change Tracking'!$D$2,FIND(" ",'Change Tracking'!$D$2,1)-1),'Change Tracking'!$6:$6,FALSE),,,"Change Tracking")),7,1,1,1))

    Re: Make cell return error if blank

    Also, I forgot to mention. The cell where the changes are entered doesn't directly feed to the charts. The following formula is used as a named range to grab the changes for use in the charts:

    =OFFSET(INDIRECT(ADDRESS(6,MATCH(LEFT('Change Tracking'!$D$2,FIND(" ",'Change Tracking'!$D$2,1)-1),'Change Tracking'!$6:$6,FALSE),,,"Change Tracking")),7,1,1,1)

    I'm trying to find a way to make this error if the offset returns 0


    I have a change tracking option for a chart which can have a comment and date entered by users to display a vertical line and data label on the chart.

    My problem occurs when the cell for the date is blank. When the cell is blank, the chart plots the line at 0/01/00 and completely kills the format of the chart. I can't use a fixed range on the scale as the charts are dynamic with a drop down box and can have a widely varied range.

    The other issue is that I can't enter it as a formula because it will get typed over when a change is made and then if its deleted afterwards I end up with the same problem.

    I've looked at a fair few sites but I can only find methods for shown a blank cell when something errors, not to show an error instead of a blank cell.

    Alternatively, if there's a way to hide/remove the change series if the x axis range is empty that would also work. Or even if its possible to make it so that certain series can't affect the axis scale. I've tried putting it on the secondary axis and that sort of works...but when something is entered the line doesnt end up in the right place

    Any help is appreciated

    Re: Find/match text from one cell to the same text in a certain row.

    Still haven't figured out why it doesn't work...but i'm using a hidden sheet to output the named ranges and then just a static range in the chart to get around it. It just seems really weird since the formula works but it won't let me use the named range in a chart.

    Re: Find/match text from one cell to the same text in a certain row.

    Thanks, I didn't realize that the address function had that in-built...was looking into concatenate.

    My function is now:
    =OFFSET(INDIRECT(ADDRESS(6,MATCH(LEFT('Change Tracking'!$D$2,FIND(" ",'Change Tracking'!$D$2,1)-1),'Change Tracking'!$6:$6,FALSE),,,"Change Tracking")),7,1,1,1)

    But when I type: "='Bookname.xlsm'!RangeName" into the chart axis value box it gives me a formula error again. I've entered the formula on a cell in the same sheet in the graph and it give me the correct date that im looking for but for some reason the graph doesnt like it

    In case anyone asks, yes my workbook does have macros so xlsm is correct =)

    Re: Find/match text from one cell to the same text in a certain row.

    Quote from widgetwonka;555495

    You need an indirect to tell excel you are using a reference:

    =OFFSET(INDIRECT(ADDRESS(6,MATCH(LEFT($D$2,FIND(" ",$D$2,1)-1),6:6,FALSE))),2,1,1,1)

    That makes sense, I killed the indirect function out of my formula at one stage while I was trying to work things out and forgot that I needed it. Although now it just outputs FALSE. I'll keep working to figure this one out, i'm guessing its due to the match or something else.

    Ok I stepped through the formula and for some reason it was setting the whole thing equal to itself a few times...copied the formula out and pasted to a new cell and it works fine.

    Thanks a lot for the help

    I had another problem but i've figured out what's casuing it...The formula looks in B6 of the currently selected sheet to find a match. But the graphs I need to use this in are on other sheets. I'll try to find a way around this problem

    Re: Find/match text from one cell to the same text in a certain row.

    Thanks, that works great. I was looking at the address function but couldn't figure out how to use it properly


    Hmm...well your function works great but when I put it into the offset one I get an error.

    Here's my formula:

    =OFFSET(ADDRESS(6,MATCH(LEFT($D$2,FIND(" ",$D$2,1)-1),6:6,FALSE)),2,1,1,1)

    If I do the address part on its own it returns $B$6
    If I type $B$6 into the reference for OFFSET it works fine, but directly like this doesnt seem to work.

    For now I might just try a workaround and link the reference to a cell with the address function but if you can see anything wrong please let me know.