Posts by Rennie

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi James,

    Copy this formula into B1 to format Cell A1. You can copy this down for the other cells in column A.


    Hope this helps,

    Hi Mike,

    This is a difficult question.Why? you are not in control of people creating those files with different dilimiters.

    As you say one of the options is to open it in a text editor and subsequently change it or alternatively to communicate your need to those file creaters.

    Hope this helps.


    Hi Laan,

    Welcome to the board.

    Please try the following.

    1. Make a backup of your file

    2. Insert a new worksheet after the worksheet you want to convert.

    3. Assuming the sheet1 as the sheet containing you data to be converted. Go to the newly inserted sheet and type in the following formula for the corresponding cells you want to convert.

    =" (please key in 99 spaces here) "&Sheet1!A1

    Note this formula merely inserts 99 spaces in front of each cells data.

    Copy this formula to the corresponding range you want to convert.

    4. Now click on the newly inserted sheet and save it as a text file.

    This should work.

    Hope this helps.


    Hi Yogendra, sorry for not giving you feed back any earlier.

    Since my last post to this string the following events have taken place.

    1. my magificent spreadsheet with all the links crashed , returning the error "out of memory"

    2. I searched this forum for "out of memory" and discoverd a wonderful technique that was posted by our great Dave!

    3. I have modified daves code to possibly achieve a consolidation workbook.

    Please copy the two attached files in the same location and run the TestDW2 macro. The DH_Macro in module 1 is what Dave Suggested (Bloody good).

    Please update this forum if you have any smart ideas.


    Assume the following

    1. Your Cell 1 and Cell 2 in the original question are in Workbook1-Sheet1.

    2. The "Post Retirement Benefits" sheet is in Workbook1.

    Please do the following.
    1.Select the cell you want to add from "Post Retirement Benefits" sheet. (i.e. $F$73)

    2.Select Sheet 1 Cell3 and Edit>pastespecial>lins

    3.Copy Cell3

    4.Select Cell1&Cell2 and edit>pastespecial>add

    This should work.


    Hi lionheart,

    One way of doing this using the paste special function.

    Try the following.

    Put the section you want to include in Cell3.

    i.e. Cell3='Post Retirement Benefits'!$F$73

    Copy Cell3.

    Select Cell1 and Cell2.

    Select -Edit>pastespecial>add

    this should do the trick.

    Hope this helps.


    Hi Srivatson,

    I am not a VBA guru. However, the following code seems to work is your data range does not have the item you are trying to delete.

    May be other VBA gods here could help refine this.

    Sub DelUnwantedField()

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Fruit")
    .PivotItems("Apple").Visible = False 'This is the item you want to delete
    .PivotItems("Apple").Delete 'This syntax seems to delete the unwanted option!
    End With

    End Sub

    Hope this helps.



    This formula should put the sheet name where ever you want.


    Hope this helps.


    Dave I have setup a location called - C:\Ozgrid Likom\Testings\Book3.xls in my computer but am unable to get the desired result.

    Could you kindly send me the file that you are linking from and linking to please.

    Appreciate your help.

    Hi Neale and Yogendra thank you for your responses.

    Neale yes I have around 20 workbooks each one has around 400x60 cells that are linked to the consolidation workbook.

    Yogendra, thank you I will try this out and get back to you. It is Saturday in Oz got to do the family thing! if you know what I mean.


    I would like to know the best way to extract data form multiple workbooks in to one single workbook.


    workbook 1 = Named range = R1
    workbook 2 = Named range = R2

    In workbook 3 I would like to copy R1 and R2 one below the other and create range R3 or create a live link so that I can analyse the data using pivot tables.

    Currently I have copied links from R1 and R2 to make named range R3.

    Is there a better way of doing this using VBA.

    I would very much appreciate any comments.


    Hi ncrist,

    Try typing in this formula in the adjacent cell. =IF(LEFT(YourCell,1)=":",REPLACE(YourCell,1,1,"0:"),YourCell).

    I am assuming that there isn't a space before ":".

    Hope this helps.


    My thick brain can not still understand what is required. I will keep working on it and get back to you.

    But I have pasted an extract from excel help that may help you.


    I am trying to join 2 IF statements together

    Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

    If AverageScore is Then return
    Greater than 89 A
    From 80 to 89 B
    From 70 to 79 C
    From 60 to 69 D
    Less than 60 F

    You can use the following nested IF function:

    In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

    I am sorry if you have already seen this.