Refers To A Group Of Cells In Another Workbook

Mar 11, 2007

I have some code which refers to a group of cells in another workbook as follows:

Workbooks("SUMMARY06.XLS").Worksheets("JS").Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value

I wish to change the reference to the sheet reference rather than the tab name, but the following doesn't work.

Workbooks("SUMMARY06.XLS").sheet103.Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value

And if I make two or more references to the same Workbook, can I use a with statement, like:

With Workbooks("SUMMARY06.XLS")
.Worksheets("JS").Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value
End With

View 9 Replies


ADVERTISEMENT

Formula In The Name Define In Workbook That Refers To Another Workbook

Jul 26, 2009

I have a formula in the Name Define part of excel in workbook that refers to another workbook. =IF(COUNTIF([DaysWorking.xls]Sunday!$A:$A,'2009'!I1)>0,1,"")

The DaysWorking.xls workbook is a data workbook, users may change the data and after they are done, they may exit the program but not save it. The DaysWorking.xls workbook should always revert back to the original form. I've put a read only property on it, but the problem lies when the user saves the file under Save/As function. Excel then updates the link to whatever the user saves the file as. Is there a way to make sure that the formula never get's changed?

View 2 Replies View Related

Writing Formula Which Refers To Different Pages In Workbook?

Dec 29, 2013

I'm working with a multi-page workbook I use for work scheduling purposes.

This is how Sheet1 is set-up:

A
B
1
Name

[Code]....

In the cells in Column D I have used the following formula =IF('Sheet1'!B2="Yes","Holiday",""). What I have realized is that this formula is quite limited. For example not all 12 colleagues work everyday and they work different shifts on different days- each day's data is sorted so that the colleagues are organized primarily by Shift start time and then alphabetically by name- consequently if I used this formula across the entire workbook the Sheet1B2 data may not be consistently referencing the same colleague.

I was wondering if there was a way to amend the formula so that if a colleague is marked as 'Yes' in Sheet1, 'Holiday' could be inserted in Column D in the rows corresponding to where the colleague's name appears?

View 3 Replies View Related

Add Hyperlink To Shape That Refers To Website Contained In External Workbook Cell

Apr 13, 2013

I've been trying without success to hyperlink a shape in one workbook that refers to a website contained in a second workbook cell.

The hyperlinked shape is in the current workbook and the website address is in a workbook called "data source.xlsx", sheet 1, cell A1.

My last attempt was this - ('C:UsersLouiseDocumentsMA[data source.xlsx]Sheet1'!A1)

View 3 Replies View Related

Concatenate Formula: Refers To A Cell Containing 0.065formula Refers To A Cell Containing 0.065

May 3, 2006

is it possible to concatenate in such a way that formatting is retained? For example, my =CONCATENATE formula refers to a cell containing 0.065, but which is formatted as a % so that the cell shows 6.5%. Concatenating that with text, however, produces "0.065 [text]"

View 4 Replies View Related

Change Color Of Group Of Cells Based On Data In Another Group Of Cells

Jan 12, 2014

Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:

If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.

View 2 Replies View Related

Pivot Table Group :: Fill Missing Cells With A Data On Workbook Open

Sep 18, 2008

new data goes into the report, the pivot table looks at a dynamic range and confirmed that the range doesn't select any empty cells.

Just wrote a macro to fill missing cells with a data on workbook open.

all the data is date form, yet still when i refresh the pivot table i look my montly grouping, when i try to group again it says "unable to group"

View 9 Replies View Related

Design If Statement That Refers To A Range Of Cells On Calculation Sheet

Mar 21, 2014

I'm trying to design an if statement that refers to a range of cells on a calculation sheet to perform one calculation on TRUE; another on FALSE

=IF(A22='Calculation Sheet'!A27:A36,B22*100/16,B22*100/8)

When I view the formula in Function Arguments window, Value_if_true = 1043.75 & Value_if_false = 2087.5 <- these are correct

IF Func Arg.jpg

When I view the cell in the spreadsheet, system displays #VALUE!

When I view Evaluate Formula window, it seems Excel doesn't like the text.

IF Form Eval.jpg

View 5 Replies View Related

VBA To Search Group Of Cells And Input Message If Value Is Missing From Group?

Mar 17, 2014

I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.

View 11 Replies View Related

Group/Ungroup In A Protected Shared Workbook

Aug 20, 2008

I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?

Question: How can I group/ungroup columns in a shared (and protected) workbook?

View 6 Replies View Related

Userform Refers To Textbox?

Jun 20, 2014

I'm having a problem referring to the value in my text boxes. I'm trying to refer the value in those to certain columns and rows that will be deleted after.

[Code]......

View 7 Replies View Related

Refers To A Dynamic Named Range

Jan 13, 2010

My file has a code that refers to a dynamic named range. When I run the macro that range gets corrupted and I cannot figure it out why. before the macro the named range refers to:

=OFFSET(Manager!$A$1,0,0,MATCH("*",Manager!$A:$A,-1),3)

and right after I run the macro it turns to:

=OFFSET(Manager!#REF!,0,0,MATCH("*",Manager!$A:$A,-1),3)

View 2 Replies View Related

Lookup With 2 Refers And 2 Data Ranges

Dec 2, 2009

I need to put a formula which will show me required quantities under a relevant date which is already calculated in an aside table.

In the cell E8 I want it to look up for A8 in the table A35:AR45. Then in that row to find a date which is exactly the same or smaller than E7 and bring up all summed quantities from the table A50:A55 (quantities summed must be in the same column where the relevant date was found and in the row where the requirend part number is).

In the other hand I need to doin the cell H8 to llok up for A8 in the ableA35:AR45. Then in that row to find a date which is exactly the same as in H7 and bring up all summed quantities from the table A50:A55 (quantities summed must be in the same column as the relevant date was found and in the row where the required part number is).

View 3 Replies View Related

Copy From 'Refers To' In Defined Names

Sep 22, 2006

This thread is related to this thread. Offset, Match, Max Formula In the Insert - Name - Define window:

If one wants to copy a formula from the "Refers to" box and the formula extends past the right side of the box, how does one use "select all" or mark the whole formula from beginning to end so it can be copied without messing up the formula. At the moment, when I try this, it changes the formula to include the active cell of the worksheet that's open when I use the Ctrl + c etc. I have tried Ctrl + Ins and Ctrl + a and Ctrl + c. None of these are working for me.

View 8 Replies View Related

VBA ListBox - AddItem Refers To A Range Within Sheet1

Mar 22, 2012

How I change the code below so that the ."AddItem" refers to a range within Sheet1?

For example .AddItem Worksheets("Sheet1").Range("O2:O20")

Code:

With ListBox1
.AddItem "January"
.AddItem "February"
.AddItem "March"

View 2 Replies View Related

Named Range That Refers To Active Sheet

Jun 15, 2007

is there a way to have the same named range cover a range of cells regardless of which sheet you are on? They seem to want to default to the page you are on. I want to make some VBA subroutines that will go over each sheet, and perform the same functions on each sheet. All sheets have the same format, but different data. I guess worst case scenario I can just break down and use multiple workbooks, but this seems a little less desirable. Should I be dividing this into seperate posts?

View 3 Replies View Related

Cannot Drag 2007 Formula That Refers To Table

Feb 14, 2008

i noticed that when i have a formula that refers to a table, such as:

= sumifs(table1[heading4],table1[heading1],"A",table1[heading2],true)

i can copy-paste it, i also can fill (right or left) a selected range with it,
but, i cannot copy it (right or left) by dragging.

View 3 Replies View Related

Add One Group Of Cells

Apr 12, 2008

im tyring to make excel add one group of cells. but not like 5+5=10 but 5+5=2. confused yet i am. basicly i want it to only count the number of cells with numbers in it. im shipping boxes with differnt amounts in them. but i want to know how many boxs are shipping.

View 13 Replies View Related

Pivot Table Group :: Group By Integers And Not By Months, Years Etc?

Nov 12, 2009

I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.

I've had a look at the format settings of the column and they are formatted as date fields.

View 9 Replies View Related

VB String Written Cross Refers Tab2 On A Spreadsheet

Nov 14, 2006

I had the following VB string written for me recently, that cross refers Tab2 on a spreadsheet to tab1 with any duplicated fields in Tab1 being moved to Tab3.

Column B is the column I am comparing over the 2 sheets...


Sub DIVIDEND()

Dim Master As Worksheet 'Tab 1
Dim RefTab As Worksheet 'Tab 2
Dim NewTab As Worksheet 'Tab 3
Dim Cell As Range
Dim iRow As Long
Dim jRow As Long
Dim ff As String

The addition I would like to add is after this first action has taken place, I need all all of the data that is in Tab2 and not Tab1 moved to Tab 3.

To summarise, I need any info that appears in Tabs1 & 2 moved to Tab 3 and any remaining lines that are on Tab 2 and NOT Tab1 moved from Tab2 to Tab3.

View 9 Replies View Related

Format Group Of Cells

Feb 13, 2014

I have a spreadsheet that contains data with the following format:

5 EA/BX
12 EA/PK

Unfortunately, sometimes the data is input without a space:

5EA/BX
12EA/BX

I am looking for a way to insert a space between the data when one does not exist. (I cannot have more than 1 space between the alpha numerical character and the numerical character.)

View 9 Replies View Related

Deleting The Name For A Group Of Cells

Feb 3, 2009

When you highlight a group of cells and give them a reference name- that name seems to be permanent. How do you delete one or add cells to the reference? Example attached book1.xls Cells A1-B3 are named "TheGroup".

View 2 Replies View Related

Increase Value In Group Of Cells

Mar 8, 2009

Can someone tell me it is posible to increase cels value (diferent values) from D2 to D21 for example, by 20. I just start to use excel.

View 6 Replies View Related

Add Characters To A Group Of Cells?

Sep 8, 2009

I have a column of about 3000 numbers that need to be converted to bar codes. In order for the codes to work, I need to place a set of parentheses "()" around each number in the column. Do I need to do this by hand, or is there a function/script for this? Using Excel 2003

View 2 Replies View Related

VBA Merge Cells That Contain Same Value But Within Particular Group

Mar 21, 2013

I have a question regarding automatic merging in VBA. Its similar to this thread (Macro Required - Automatically Merge vertically identical cells) but with a slight twist.

So say if I have four columns set up as follows, where the first line contains headings (before)

Team
Position
Salary
Term (years)

Arsenal
Goalkeeper
100
1

[Code] .......

Im looking for something like this (please ignore the periods as i had to put them to keep the table from incorrectly displaying). I'd like to highlight the non-merging of Striker (line 7 and 8 in column2) becasue one belongs to Manchester and the other to Arsenal

Team
Position
Salary
Term (years)

Arsenal
Goalkeeper
100
1

[Code] .......

View 3 Replies View Related

Looking For A Cell In A Group Of Cells

Jan 27, 2007

currently i have a address of a cell, i.e. "$C$3". and i want to search for this cell from a few group cells i.e. "Range1 = $A$1:$A$10", "Range2 = $B$1:$B$8", "Range3 = $C$2:$D$8". after searching for the matching cell, the macro would return the name of the group cell to me, i.e. in this case is "Range3". how would i go about doing that? could anyone enlighten me on this?

View 5 Replies View Related

Insert Group Header Based On Consistent Value Within The Group?

Feb 10, 2014

I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.

View 1 Replies View Related

How To Assign Characters For Group Of Cells

May 30, 2014

If I have 5 names like apple, boy, cat, dog, energy

and if i enter a in a cell it should output apple in the same cell.

example: (=IF(A1="a",A1=apple,0))

How to do this?

View 6 Replies View Related

Concatenate Group Of Cells If They Contain Certain Text

Jul 28, 2014

I am trying to figure out how to Concatenate a group of cells if they contain a certain text. Basically I have a list of Architects in column A and the account name in column B... and I want to say Concatenate column A, IF column B=Specific Account (ex: Coca-Cola). The list will be added to frequently so the formula must refer to the entire columns, not just a certain range.

As of right now, only the first architect associated with each account will appear. I have attached a workbook with the example..

example.xlsx‎

View 6 Replies View Related

Assign Same Value To Selected Cells Within The Same Group?

Jul 29, 2014

I have a table with 2 columns A & B and desired outcome in column C (see attached sample).

I need to assign the same value to cells (in column A) located within the same group (in column A) using the following rules:

If any cell value within the same group = W220 or W210 or E240 or E250, then assign value "Group A"

If any cell value within the same group = P210 or C100, then assign value "Group B"

If any cell value within the same group = N230 or N250, then assign value "Group C"

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved