Formula Problem

Joe, do you want to maintain the value of 90,000 but just show 90 in the cell? If so, custom format the cell to

#,

This will show 90 for value of 90,000. Note, any multiples of 1,000 will be rounded. For example, 90,500 will show 91.

Regards,
BarrieBarrie Davidson

Is it as simple as having a formula that works something like this....

=[otherfile.xls]Sheet1!$E$16/1000

If cell E16 in otherfile.xls is 90000 then the above formula will return "90". You can reduce your decimal places to zero if the cell is formatted for 2 (by default).

Or is your situation more complex than that?

Todd

Scratch that....here's the real problem

Ok, I'm writing a macro that copies a formula from one workbook to another, the divide by 1000 is simple I just divide the formula value by 1000 in the macro like so:

ActiveCell.FormulaR1C1 = "='[2207s_Log_Master.xls]2207'!R10C4/1000"

Now here is the real problem. I need the macro to work on whichever workbook is open, not just 2207s_Log_Master.xls. So I need like a NextActiveWorkbook command or something in place of 2207s_Log_Master.xls....is there something for that???

As another problem, the formula in the macroed to workbook still needs to read off of whichever workbook was indeed open at the time the macro was run....so if I put a variable workbook command into the formula that will screw up when I go to open the copied to workbook and it attempts to update the information.

Anyone have any ideas??? My big thanks if so...I hope this wasn't to confusing!

Hmmm as a side question...

If I goto format cells and set it to Number, it only allows for setting decimal places after the 90,000 (like 90,000.00)....so how then do I make it read 90 like this?

Thanks.

Use custom #,

Re: Scratch that....here's the real problem

Joe, a couple of questions.

1. What happens if you have more than two workbooks open (say you have five)? Where do you get your data from?

2. What about querying the user to input the data file where you are extracting the data from (something like the file open box)? Would this work for you?

Barrie
Barrie Davidson

Paul..

I must be doing something wrong....I goto format cells, then choose custom, but I can't find any custom option that allows the number 90000 to be read like 90....hmmmm.

Re: Scratch that....here's the real problem

Barrie,

First off thanks for all the help. The way I'm writing the macro is to make sure that the only two workbooks that are open are the ones that you want to link, for lack of a better idea...

I like your query idea I was thinking of something like that. Unfortunately this is my first week self teaching myself how to use macros so I'm at kind of a loss as far as advanced macro writing....I think I'll have to go buy a book....any suggestions?

Thanks again for the tips!

Joe

Re: Paul..

Joe, when you choose custom you need to type in

#,

in the "Type" box.

BarrieBarrie Davidson

AHA!

Thanks thats what I wasn't doing!

Missed one additional question

Is there only one worksheet in the data file (2207s_Log_Master.xls)?

BarrieBarrie Davidson

Re: Missed one additional question

Barrie,

There are 50 worksheets in the data file...

Re: Missed one additional question

How do you determine what worksheet to select your data from (2207, in your example).

BarrieBarrie Davidson

Re: Missed one additional question

I'm making sure that the active worksheet (2207 in this case) is the worksheet active. I'm switching between workbooks by using the ActiveWindow.ActivateNext command. If say I want to copy a different worksheet from the 2207s_Log_Master.xls I just switch the active worksheets.

To maybe help with a little background on what I'm doing, I have 5 people who will be working with 5 Workbooks with identical templates. Each of these workbooks has 50 worksheets that are used for tracking a budget.

The macro copies specific cells of information on the worksheets and summarizes them on a table in a 6th workbook. Each time a new worksheet is generated by one of the 5 people, the macro is run to copy its information onto a new line of the summary table. This information from the worksheets is constantly changing so when I pull up the 6th workbook it needs to automatically update the information.

So you can see my problem. The macro only wants to pull information from the worksheet I recorded it on. I need to figure out a way to get the formula to recognize whichever workbook is open, and its corresponding active worksheet.

Sorry for the novel! I hope that helps more...

Re: Missed one additional question

Okay (sorry for the delay in replying), try something like this:

Sub InputFormula()
' Written by Barrie Davidson
Dim CurrentFileName As String
Dim OtherFileName As String
Dim OtherFileSheetName As String

CurrentFileName = ActiveWorkbook.Name
OtherFileName = Windows(2).Caption
OtherFileSheetName = Windows(2).ActiveSheet.Name
ActiveCell.FormulaR1C1 = "='[" & OtherFileName & "]" & OtherFileSheetName & "'!R10C4/1000"
End Sub

BarrieBarrie Davidson