need to copy cell refs with different incrementation

I need to copy a range of cells, and some of them have references t
cells on another worksheet, but the cells with references are no
contiguous, nor are the referenced cells.  I have tried the fill handl
to duplicate, and it does it partially correct, but not completely.

Example: B7 contains cell reference =eb!A4 (eb is the name of the othe
worksheet), and C9 contains cell reference =eb!B5.    Cell B10 contain
the cell reference =eb!A8, and C12 contains =eb!B9.  I want to cop
these six cells over and over down the page.

The problem is that I need the B cells to increment the number by thre
over the numbers in the C cells.

When I use the fill handle, if I have highlighted, say,  6 cells
B7:C12, it only increments the number in B13 to =eb!A10, one digit mor
than C12's =eb!B9.  I need it to increment it to =eb!A12.

Of course, I tried highlighting 9 cells with the correct numbers i
them,and all it does is basically repeat the problem every 9 cells.

Hope I have made this clear.  Doesn't seem too clear to me after typin
it...

Thanks.

dmv

--
Message posted from http://www.ExcelForum.com

0
4/20/2004 5:18:17 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
549 Views

Similar Articles

[PageSpeed] 14

Hi
you may try to explain the logic after your increment - I didn't get it
:-)

--
Regards
Frank Kabel
Frankfurt, Germany


> I need to copy a range of cells, and some of them have references to
> cells on another worksheet, but the cells with references are not
> contiguous, nor are the referenced cells.  I have tried the fill
> handle to duplicate, and it does it partially correct, but not
> completely.
>
> Example: B7 contains cell reference =eb!A4 (eb is the name of the
> other worksheet), and C9 contains cell reference =eb!B5.    Cell B10
> contains the cell reference =eb!A8, and C12 contains =eb!B9.  I want
> to copy these six cells over and over down the page.
>
> The problem is that I need the B cells to increment the number by
> three over the numbers in the C cells.
>
> When I use the fill handle, if I have highlighted, say,  6 cells,
> B7:C12, it only increments the number in B13 to =eb!A10, one digit
> more than C12's =eb!B9.  I need it to increment it to =eb!A12.
>
> Of course, I tried highlighting 9 cells with the correct numbers in
> them,and all it does is basically repeat the problem every 9 cells.
>
> Hope I have made this clear.  Doesn't seem too clear to me after
> typing it...
>
> Thanks.
>
> dmva
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/20/2004 5:33:50 PM
I didn't think it was too clear.

Here's the pattern I want to establish all the way down the page:

B7 needs to contain =eb!A4
C9 needs to contain =eb!B5
B10 needs to contain =eb!A8
C12 needs to contain =eb!B9
B13 needs to contain =eb!A12
C15 needs to contain =eb!B13
B16 needs to contain =eb!A16
C18 needs to contain =eb!B17
B19 needs to contain =eb!A20
C21 needs to contain =eb!B21

If I copy any multiples of these 3 cell ranges, the fill handle onl
increments the B number by one over the C number after that number o
multiples has been copied.  In other words, if I highlight and dra
fill handle with B7:C12, then B13 is entered as =eb!A10, not =eb!A12.
If I highlight and drag fill handle with B7:C15, then B16 is entered a
=eb!A14, not =eb!A16.

I hope you can see what I am trying to accomplish.

Thanks for the help.

dmv

--
Message posted from http://www.ExcelForum.com

0
4/20/2004 5:56:11 PM
Hi
try the following formulas:
B7:
=OFFSET('eb'!$A$4,INT((ROW()-7)/3)*4,0)

C9:
=OFFSET('eb'!$B$5,INT((ROW()-9)/3)*4,0)

now select these cells and copy them


--
Regards
Frank Kabel
Frankfurt, Germany


> I didn't think it was too clear.
>
> Here's the pattern I want to establish all the way down the page:
>
> B7 needs to contain =eb!A4
> C9 needs to contain =eb!B5
> B10 needs to contain =eb!A8
> C12 needs to contain =eb!B9
> B13 needs to contain =eb!A12
> C15 needs to contain =eb!B13
> B16 needs to contain =eb!A16
> C18 needs to contain =eb!B17
> B19 needs to contain =eb!A20
> C21 needs to contain =eb!B21
>
> If I copy any multiples of these 3 cell ranges, the fill handle only
> increments the B number by one over the C number after that number of
> multiples has been copied.  In other words, if I highlight and drag
> fill handle with B7:C12, then B13 is entered as =eb!A10, not =eb!A12.
> If I highlight and drag fill handle with B7:C15, then B16 is entered
> as =eb!A14, not =eb!A16.
>
> I hope you can see what I am trying to accomplish.
>
> Thanks for the help.
>
> dmva
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/20/2004 6:06:27 PM
Frank -

Thank you for your help.

This does seem to work, but unfortunately, I won't be able to tell fo
sure until later today, after the information I am copying from on th
first sheet becomes available.  At the moment, I am working with onl
one set of 3 cell ranges, and am taking my best shot at guessing wha
the increment needs to be.

That said, I wonder if you could explain your formulas a little more?
Especially since I might have to modify them in a hurry.

I looked at the syntax for the =offset in Help, and can see th
arguments you have for the reference (eb!$A$4 or eb!$B$5), and th
columns would of course be 0.  But, I'm not sure I understand exactl
the part you have in there for the rows argument.

Perhaps I'm just having an early senior moment!

Again, I appreciate you help!

dmv

--
Message posted from http://www.ExcelForum.com

0
4/20/2004 6:50:18 PM
Hi
within the row argument i take the current row (ROW()) and subtract 7
(this is the row of your first cell: B7). I divide this result by 3 and
multply it with 4. Reason:
you insert this formula every third row (B7, B10, B13) and increment
the source cell row by 4

--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
> Hi
> try the following formulas:
> B7:
> =OFFSET('eb'!$A$4,INT((ROW()-7)/3)*4,0)
>
> C9:
> =OFFSET('eb'!$B$5,INT((ROW()-9)/3)*4,0)
>
> now select these cells and copy them
>
>
>
>> I didn't think it was too clear.
>>
>> Here's the pattern I want to establish all the way down the page:
>>
>> B7 needs to contain =eb!A4
>> C9 needs to contain =eb!B5
>> B10 needs to contain =eb!A8
>> C12 needs to contain =eb!B9
>> B13 needs to contain =eb!A12
>> C15 needs to contain =eb!B13
>> B16 needs to contain =eb!A16
>> C18 needs to contain =eb!B17
>> B19 needs to contain =eb!A20
>> C21 needs to contain =eb!B21
>>
>> If I copy any multiples of these 3 cell ranges, the fill handle only
>> increments the B number by one over the C number after that number
of
>> multiples has been copied.  In other words, if I highlight and drag
>> fill handle with B7:C12, then B13 is entered as =eb!A10, not
=eb!A12.
>> If I highlight and drag fill handle with B7:C15, then B16 is entered
>> as =eb!A14, not =eb!A16.
>>
>> I hope you can see what I am trying to accomplish.
>>
>> Thanks for the help.
>>
>> dmva
>>
>>
>> ---
>> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
4/20/2004 7:15:27 PM
I think I've pretty well got it now.

Thanks again, Frank, for all your help.

dmv

--
Message posted from http://www.ExcelForum.com

0
4/20/2004 7:46:45 PM
I think I've pretty well got it now.

Thanks again, Frank, for all your help.

dmv

--
Message posted from http://www.ExcelForum.com

0
4/20/2004 8:24:23 PM
Reply:

Similar Artilces:

Cannot syncronize accounts in different currencies on my pocket
Hello, I often need to manage accounts in different base currencies but it seems that accounts different from base currency, do no appear on pocket pc 2005. I am using money 2005 on my desktop and recently downloaded pocket money 2005 to my ipaq. This seems to be a serious limitation. any suggestions? Thanks It is a (one of the) limitation of the pocket pc client. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://regi...

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

Copy Constants Only
Hi, Need to copy all "values" or "constants" (not Formulas) from one sheet to another sheet in a different workbook. I did the following: Edit -->Go To then Special (from dialogue box) then "constants" I see all the cells that I want are selected. Then I tried to use the copy command but I got the error message: "that command cannot be used on a multiple selection". Any ideas is appretiated! Khalil Here is a method that works, Copy everything to the second sheet Not use Edit | Go To | Special | Formulas and tap the Delete key best wishes -- Ber...

Need help with Excel Form & ComboBox Tutorial
At http://www.excel-vba.com/v-forms-controls.htm I have followed instructions... my code on the form is below but it won't run... I've marked the error... Can anybody give me any help with this? thanks Code is below-------------- Private Sub cmdBtnSubmit_Click() shReport.Range("C4").Value = cbxCity.Value cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub cmdCityCancel_Click() cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub UserForm_Activate() shParameterst.Activate '<-----Run Time Error 424 - Object Required...

Need to set up a slide with 4 text boxes on same page.
Want to end up with 4 "bulleted" boxes that I can to show 4 strategies and associated task on same page. Also, if possible have each one drop in individually to allow flow conrol for the presentation Are you asking a question about how to do that or having trouble with part of that? If the former, just create four separate text boxes with bulleted text, you are not limited to only one text box per slide. Use the Custom Animation, Effects Options, Text Animation settings to control the entrance of the bullet points. Since you didn't say what version of PowerPoi...

Need to populate a Report with several records
In short, I have one report containing 5 records from the same table. The individual records are layed out to support proper printing. I need to populate each record with from the same table. I can not give them all the same pointer to the table or I get 5 copies of the same record. How can I populate those 5 records with 5 records from my table? I think you need to have a group by in your report. Thus you will get 1 group of data for each of the 5 that you are refering to as 5 records. "Bill" wrote: > In short, I have one report containing 5 records from the same t...

Crystal Report error: information is needed 02-03-04
After reinstalling CRM I get this error when I try to get a report. I know there is information since I'm just asking for the user list. ---------------------------------------- CrystalReportViewer Information is needed before this report can be processed. Information is needed before this report can be processed. ---------------------------------------- Any Ideas? Thanks Also recieved this error, and have logged an issue with MS, no resolution yet. One tip they did provide, remove any underscores ("_") from the server name. This has resolved the issue for a lot ...

Label lines disappear when I copy to Powerpoint
Just created a pie chart in Excel 2007 with Labels which have a line joining the label to the pie segment, however when I copy and paste into Powerpoint the lines joining the labels to the segments disappear. Anyone know how to fix this? Need presentation for tomorrow! __________ Information from ESET Smart Security, version of virus signature database 4031 (20090423) __________ The message was checked by ESET Smart Security. http://www.eset.com ...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

I lost my Access (Office) 2003 disk
I have MS Office 2003 loaded on my computer, but someone stole all my program disks just over a year ago, and now I'm finding that things like the subform wizard doesn't load automatically with the program, but has to be loaded later from the program disk (I'm working out of some tutorial-type books for Access 2003). Is there some way of getting around this? Either just using Access workarounds, or is there some way of getting a hold of these (apparent) add-ins to the program, either on line or on disc? Thanks Rob, I answered your question in the Forms newsgroup, this morning. -...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Empty copy of database
G'Day All,I have a back-end database that contains both client data tables andsupporting tables with lists (e.g. Languages, Income Ranges etc) forcombos on the Forms. All relationships are defined, including twowith cascading deletes.I would like to take a "clean" copy of the database with client dataremoved and all autonumber key fields reset (to 1 for the first record).How may I achieve this?-- Regards,Pat GarardMelbourne, Australia_______________________ Create a new (blank) database.Import all tables: File | Get ExternalIn the Import dialog, click the More/Options button (...

Copy and paste from Excel to GridView
Hello, is it possible to copy and paste values taken from an Excel spreedsheet into the grid of a GridView in a ASP.NET page? Luigi On May 21, 4:29=A0pm, Luigi <Lu...@discussions.microsoft.com> wrote: > Hello, > is it possible to copy and paste values taken from an Excel spreedsheet i= nto > the grid of a GridView in a ASP.NET page? > > Luigi You mean from a single cell or entire table? The standard gridview control is a data-bound control for rendering data. It has no paste function. To copy and paste values from Excel you can use a separated textbox...

Cell QA for Bob Phillips
Good afternoon Bob: In reference to your QA: (Which sheet was the active sheet when you right-clicked on the tab? It should have been Data Sheet). Yes, I did as you said, but I am not sure I placed the text string in the right place. I pasted it in View Code on the blank area to the right of "Project / Properties" section but for some reason it is not moving the text from "Data Sheet" to the "Activity Sheet". Is it possible to communicate with you directly so I can get this worked out? Thanks John ---------------------------------------------------...

Simple question
I have an xls sheet that always print 6 copies. When ever I want to print, I have to change it to 1 copy. It's only this one xls sheet. Everything else prints 1 copy like it's suppose to. It's not the printer settings. Like I said, it's just this one xls sheet. Can someone tell me how to make it print 1 copy only and then stay as 1 as the defualt. I have made many changes to the sheet and saved it many times, but it always defaults to 6 copies. Very Strange. Thanks in advance. Phil If maybe as simple as this, as each page can have the page count se individua...

need Blank column in invoice
Hi, How can I make(or hide) contents of cell unless the cell before it isfilled in? Hope i said that right. My invoice has a payment col and balance col next to each other,. I want to have the formula in the balance column all the way down, but it should be emty until a payment is filled in? Thanks Frank Frank Trap for no entry in the payment column by entering a formula in the balance column. =IF(payment_cellref="","",balance_formula) Gord Dibben MS Excel MVP On Wed, 19 Apr 2006 21:48:18 -0400, "Frank" <stratster68@IHATESPAMworldnet.att.net> ...

Need help with code...
I have a problem. I have a drop down combo box called "Query status" with two options: "outstanding" and "completed". The record can't be changed to "completed" until certain other fields have ALL been entered but there is an extra complication. One other combo box can be either "other" or "invoice". Two extra fields need to be entered if this combo is "invoice" otherwise they aren't mandatory. In full this is the code I currently have in the "after update" event of each field: If Me.Qry_QryType =...

how do I get the if function to return a blank cell, not 0?
I am trying to create a chart from a series that contains data for each month. The series is calculated on other worksheets and copied to the worksheet containing the chart. I would like to have the cells for the months that have not been updated yet (now is January, there are 0' in all cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's. Can this be done? Unfortunately, what you want, and what many of us have requested but doesn't exist, is a worksheet function like BLANK() or NULL(). The best we can do is use NA() in a chart's data source, which is...

CRM 3 - User Transfered All Cases to Own Name
We had a user that left the company. Another user transfered all of his cases (resolved / active) to his name. He some how reassigned all the resolved and active cases in the DB to his own name. Is there a transaction table for changes to cases? Or a way that we can roll back the transfers? Unfortunately, we do not have a back up. CRM does not offer a transaction logging sollution out of the box. The SQL database does, but this has to be configured and can only rollback all changes not specific ones. A thing you can try is (using the SDK) retrieve the last activity for each case ...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...

different number format
Hi, this is probably an easy question but I am at a loss been an excel newbie. I have XP with my local regional settings, where the number format is 1.000,29 (onethousand and 29/100) on a specific excel sheet I need to revert these settings, in order to paste a large amount of data 1000.29 (onethousand and 29/100) anybody can suggest how to do this? TIA Hi Daniele, I think you shouldn't change your setting, paste the data and then use the code below to fix the numbers: Sub TextToNumberOnActiveColumn() Dim r As Integer Dim c As Range r = ActiveCell.EntireColumn.Range(&qu...

help needed
Hi Please help on the following issue: I have to check if one of the check boxes in the following cells is checked I need a macro that checks if the check box in cell G14 is checked in sheet Account Tracker. If its checked then I want to do the formula in G17, if the check box in H14 is checked I want to do the formula in h17 & if the checkbox in I14 is checked I want to do the formula in cell I17. A second thing I need is that based on the above logic as there will be only one set of values in g17 or h17 or i17 .If the values in g17 or h17 or i17 are negative than I ...

How to change default cell formats
When I open a file, all negative values are shown with a - before them, whilst my colleagues, when opening the same file, the negatives are shown in brackets ie. ( ). How do I ensure that when I open the same file, my negative numbers appear in brackets This is a windows setting. Close excel Change that windows regional setting|currency tab|Negative currency format to show ($1.1) Open excel You may have to reformat: Format|Cells|number tab|Number (adjust the decimal places and comma options) Easty04 wrote: > > When I open a file, all negative values are shown with a - before t...

Default print is 20800 copies!
I'm using Excel:Mac 2001 on a Mac running OS 9.2 When I open the document and print, it prints one copy. However, when I send the document to colleagues using Excel X for Ma on OS X, and they hit print, the default number of copies shows a 20800. (And if they don't pay attention, the printer dutifully start printing the twenty thousand copies). Any solutions? Where can I adjust the default print number in Excel X? Thank -- Tim_ ----------------------------------------------------------------------- Tim_E's Profile: http://www.excelforum.com/member.php?action=getinfo&user...