|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reference another workbook and return values from the same cell across multiple sheets/tabs
Hi Everyone
I'm trying to summerise daily sales figures in a new wkb(destination wkb), by referencing another wkb(source wkb) which contains sheets/tabs named in date order(such as 01.04.14, 02.04.14 etc). On each of the source sheets/tabs, the total daily figure is in cell F6. On the destination sheet, I've labelled the columns 1 , 2, 3.....to represent each day and the months on the rows. I intend not to open the source wkb when using the destination wkb. I've tried referencing the source wkb ,however, it couldn't copy across the sheets/tabs. Any ideas as how I could fix this? |
#2
|
|||
|
|||
Hi
without opening the source-wkb, you only can do it using formulas the like you use already in B2. The formula has to contain the exact and complete path, sheet and celladdress, you can't even use the function INDIRECT(). That's the only way if the source has to remain close. Cheers |
#3
|
|||
|
|||
Hi Whatsup
Thanks for the reply. If I keep the source book open, how do I make the formula in B2 lookup all the sheet/tabs in the source wkb and return the values to the destination wkb as described above. |
#4
|
|||
|
|||
Here an example how you can insert either a formula or a value from another workbook into your workbook of destination.
With your provided uploads, you will run into an error after the third loop, since your source doesn't provide sufficent Sheets. But it will show you, how you can assemble parts of a path to a working formula, or use them to get values of an open workbook. Copy the code in a new module and step through the code using F8 while observing the changes in your sheet. Code:
Sub Get_Data() Dim lngCol As Long Dim lngMonth As Long Dim lngStart As Long Dim strformula As String Const strCellRef As String = "$F$6" With ActiveSheet lngStart = .Cells(2, 1).Value lngMonth = Month(.Cells(2, 1).Value) Do While Month(lngStart + lngCol) = lngMonth 'Insert formula .Cells(2, lngCol + 2).Formula = "='[Source WKB.xlsx]" & Format(lngStart + lngCol, "DD.MM.YY") & "'!" & strCellRef 'Insert value .Cells(2, lngCol + 2).Value = Workbooks("Source WKB.xlsx").Sheets(Format(lngStart + lngCol, "DD.MM.YY")).Range(strCellRef).Value lngCol = lngCol + 1 Loop End With End Sub |
#5
|
|||
|
|||
This really works with both wkbs open. Thanks
|
#6
|
|||
|
|||
... and that way it works with the closed source (you will have to adjust strPath):
Code:
Sub Get_DataOfClosedWB() Dim lngCol As Long Dim lngMonth As Long Dim lngStart As Long Dim strformula As String Const strPath As String = "H:\Excel-Forum-temp-MSEnglish\" Const strCellRef As String = "$F$6" With ActiveSheet lngStart = .Cells(2, 1).Value lngMonth = Month(.Cells(2, 1).Value) Do While Month(lngStart + lngCol) = lngMonth 'Insert formula .Cells(2, lngCol + 2).Formula = "='" & strPath & "[Source WKB.xlsx]" & Format(lngStart + lngCol, "DD.MM.YY") & "'!" & strCellRef lngCol = lngCol + 1 Loop End With End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
I need to add multiple values based on multiple criteria in a cell not sure what to do | AUHAMM | Excel | 3 | 10-27-2014 09:11 PM |
Reporting Values Between Sheets/Tabs | jjbNana | Excel | 3 | 10-06-2011 10:28 PM |
How to count multiple values in a single cell, except zero? | iuliandonici | Excel | 1 | 04-13-2011 09:45 PM |
Sum & difference between multiple values in a single cell | iuliandonici | Excel | 4 | 04-13-2011 09:27 PM |
How do I merge data from one sheet in a workbook out into multiple sheets | nolesca | Excel | 4 | 06-07-2010 08:13 AM |