Posts by Rennie

Hi James,

I am a bit busy now. I will post an explanation later. The #Name error was due to a gap in the Trim function. I.e - Make sure the "Trim" does not appear as "T rim".

Thanks,
Rennie

Hi James,

Sorry for the trouble. Look at the attached file.

Thanks,
Rennie

Hi,

It works if you copy the formula into B1.

Thanks,
Rennie

Hi James,

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

=(IF(ISNUMBER(A1),A1,IF(ISERROR(REPLACE(TRIM(A1),SEARCH(".",TRIM(A1),1),1,"")),VALUE(REPLACE(TRIM(A1),SEARCH(",",TRIM(A1),1),1,"")),VALUE(REPLACE(TRIM(A1),SEARCH(".",TRIM(A1),1),1,"")))))/1000

Hope this helps,
Thanks,
Rennie

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.

Thanks,
Rennie

Please try this link

http://www.mitan.co.uk

Thanks,
Rennie

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.

Thanks,
Rennie

Hi,

This should work as well.

if(A1="","",some other answer)

Hope this helps,

Rennie

Follow the thread below for more help.

http://ozgrid.com/forum/viewthread.php?tid=1895&page=2

Thanks,
Rennie

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.

Thanks,
Rennie

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.

Thanks,
Rennie

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.

Thanks,
Rennie

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()

ActiveSheet.PivotTables("PivotTable1").RefreshTable
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.

Thanks,
Rennie

Hi,

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

=RIGHT(CELL("FileName",$A$1),LEN(CELL("FileName",$A$1))-SEARCH("]",CELL("FileName",$A$1),1))

Hope this helps.

Thanks,
Rennie

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.

Thanks,
Rennie

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

E.g.

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.

Thanks,
Rennie

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.
Rennie

Thank you very much paddy exactly what I wanted.

Rennie

Katie,

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.

Quote

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:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))
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.

Thanks,
Rennie