Cell ref is it possibile to calculate / change this in a formula

Any SUPER USERS uot there?

I am trying to construct a formula where I need to copy it down 6500+ rows 
but I need one of the cell refs to be calculated in sted of having its row 
number to raise by one for each row copied.

sample,
Normel coping:
 =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
 =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26)


What I am trying to get:

 =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
 =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16)

For every line the formula is coppied down the C cell ref should show the 
row value less 11 row numbers.

Any one knows if this can be done?

-- 
;>)
0
Utf
12/11/2009 1:52:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
555 Views

Similar Articles

[PageSpeed] 44

If it is 11 rows less then the refernce should be C14 instead of C16....Try 
the below formula and copy down as required

=IF(AND(F25<=201012,F25<>$F$1),B25,
INDIRECT("'MASTER tot'!C" & 25 - (ROW(A1)-1)*11))

25 is the starting range
11 is the difference...(change to suit your requirement)

-- 
Jacob


"oliverkat" wrote:

> Any SUPER USERS uot there?
> 
> I am trying to construct a formula where I need to copy it down 6500+ rows 
> but I need one of the cell refs to be calculated in sted of having its row 
> number to raise by one for each row copied.
> 
> sample,
> Normel coping:
>  =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
>  =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26)
> 
> 
> What I am trying to get:
> 
>  =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
>  =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16)
> 
> For every line the formula is coppied down the C cell ref should show the 
> row value less 11 row numbers.
> 
> Any one knows if this can be done?
> 
> -- 
> ;>)
0
Utf
12/11/2009 1:58:01 PM
But, if you start with C25 and reduce that by 11 it will give you C14
not C16. Also, you will have negative rows the next few times you copy
it, so this will give an error, and you certainly won't be able to
copy it down 6500 rows.

You would normally use the INDIRECT function for what you want to do,
but you'll need to sort out the problems above before I can suggest a
formula for you.

Hope this helps.

Pete

On Dec 11, 1:52=A0pm, oliverkat <oliver...@discussions.microsoft.com>
wrote:
> Any SUPER USERS uot there?
>
> I am trying to construct a formula where I need to copy it down 6500+ row=
s
> but I need one of the cell refs to be calculated in sted of having its ro=
w
> number to raise by one for each row copied.
>
> sample,
> Normel coping:
> =A0=3DIF(AND(F25<=3D201012,F25<>$F$1),B25,'MASTER tot'!C25)
> =A0=3DIF(AND(F26<=3D201012,F26<>$F$1),B26,'MASTER tot'!C26)
>
> What I am trying to get:
>
> =A0=3DIF(AND(F25<=3D201012,F25<>$F$1),B25,'MASTER tot'!C25)
> =A0=3DIF(AND(F26<=3D201012,F26<>$F$1),B26,'MASTER tot'!C16)
>
> For every line the formula is coppied down the C cell ref should show the
> row value less 11 row numbers.
>
> Any one knows if this can be done?
>
> --
> ;>)

0
Pete_UK
12/11/2009 2:05:44 PM
Hi,

What happens when the formula is dragged and reduces by 11 each row is shown 
below, so you can't drag it down 6500+ rows, so what do you really want

=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
=IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16)
=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C5)
=IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C-6)

Mike

"oliverkat" wrote:

> Any SUPER USERS uot there?
> 
> I am trying to construct a formula where I need to copy it down 6500+ rows 
> but I need one of the cell refs to be calculated in sted of having its row 
> number to raise by one for each row copied.
> 
> sample,
> Normel coping:
>  =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
>  =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26)
> 
> 
> What I am trying to get:
> 
>  =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25)
>  =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16)
> 
> For every line the formula is coppied down the C cell ref should show the 
> row value less 11 row numbers.
> 
> Any one knows if this can be done?
> 
> -- 
> ;>)
0
Utf
12/11/2009 2:09:01 PM
Reply:

Similar Artilces:

coloured cells #2
Hi, I have a user that created a spreadsheet that is highlighted by colours in different cells. On his home computer he can see the colours but at work the spreadsheet becomes pure black and white. If he prints the spreadsheet at work, it is in colour. He used to see the colours on the screen at work also. He is using Excel 2002. Any ideas? Joyce Look if he has set this Click Start, click Control Panel, and then click Accessibility Options. Click the Display tab, and then click to clear the Use High Contrast check box. Click OK to close the Accessibility Options dialog box. -- ...

How do I change the color of the font shadow in Publisher2003
I want to put a mail merge field in a publisher document and highlight it with a shadow colour other than grey. Chris <Chris@discussions.microsoft.com> was very recently heard to utter: > I want to put a mail merge field in a publisher document and > highlight it with a shadow colour other than grey. Create a second text box with the merge field in, displace it slightly, send it behind the first text box and change the font colour. -- Ed Bennett - MVP Microsoft Publisher ...

Adding weekdays in a date formula
How would you add weekdays to a formula? ex. A1 = 1/1/10 If I want to add 80 WORKDAYS (excluding weekends), what formular would I use? =(A1+80) does't work... You were close to the name... =WORKDAY(A1,80) Note that you can add a third arguement to this function to define holidays, if desired. -- Best Regards, Luke M "Handy" <Handy@discussions.microsoft.com> wrote in message news:F73D7071-1877-4CAE-A17D-05EE0FDD02AC@microsoft.com... > How would you add weekdays to a formula? ex. > > A1 = 1/1/10 > > If I want to add 80 WORKDAYS...

sorting error on non-merged cells
Excel 2002, WinXp Home My spreadsheet has columns for Number, Date, Text, etc. When I select a column to sort, Excel expands the selection to include all cells (as it should). When I try to sort I get the error message about merged cells having to be the same size. I looked at KB291063 but that doesn't help. The cels are NOT merged so why do they have to be the same size? What size is the error refering to (# of characters, absolute value, ?)? How do I un-merge non-merged cells so I can sort? Firstly I would never rely on Excel to expand a selection to include the data I wa...

tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...

Changing column widths and decimal accurancy
Hi - I have 2 Q's for anyone happy to help... 1. Is it possible to change the column widths in some windows? I specifically would like to change the default width in the discount screens (Settings --> Product Catalog --> Discount Lists --> Discounts) so that "Begin Quantity", "End Quantity" and "Percentage" are all displayed on screen with a much small colum width. 2. How can I cange the default decimal accuracy in the smae discount screens from 5 decimal places to just 1 ? Many thanks ...

Formatting text in a cell
Is there a way to key text in one cell that is multiple lines? We would like to be able to insert new lines where we want them. Thanks for the help!! -- akkrug Use the Formula Bar: 1. click in the formula bar 2. in the formula bar, move the cursor to the desired point of insertion 3. touch ALT-ENTER 4. enter the new line. -- Gary''s Student - gsnu200855 "akkrug" wrote: > Is there a way to key text in one cell that is multiple lines? We would like > to be able to insert new lines where we want them. > > > Thanks for the help!! > -- > ak...

Finding combinations of cells that sum to near a value
Starting with a column of ~30 numbers, I'm looking for a way to have Excel find all combinations that sum to a target value +/- a specified range width. (For example, I may be looking for all combinations that sum to 1000 +/- 10.) I've found some code from Tushar Mehta that finds a list of all possible matches that sum to a number, but it doesn't allow me to put in a range to find sums to within a delta of this value. I tried modifying the code to add this in, but I couldn't get it to work right. Here is the code: http://www.tushar-mehta.com/excel/templates/match_values/index....

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Window Live Support
The Windows Live´┐Ż Solution Center is now available for a Windows Live Essential applications and Live Services. http://windowslivehelp.com/ 24 different categories are now available. Note: Prior links for e-form support will redirect to the above site(afaik...eform is no longer avaialable) -- ...winston ms-mvp mail Most of the posts are being posted in the future. This post was made on January 11 and it was answered today at 10:35 PM. http://windowslivehelp.com/thread.aspx?threadid=8959fac9-cacd-4ea2-833f-1bc5ec635c4f -- Ron Sommer MS MVP- Windows Live Mail &q...

Changing Tab Stop Alignment
Has anyone else noticed a problem changing tab the type of tab stop in the tab box in the corner where the rulers meet? My mouse is not changing to let me click through from one tab type to the next. All I get is a diagonal double-ended arrow and a message about double clicking to shift-drag the ruler to another location. Things are not working much better when I try to adjust margins in the ruler area, either. This is a problem in Publisher 2003-- never had this problem in previous versions. Any help or explantion would be deeply appreciated. Thanks. It is a bug... The corner tab sett...

separating multiples digit in a cell into individual cell
I have a series of number sets. When I copied from the MS Word and pasted them into Excel, all of the numbers in the set are pasted into one cell. I need those numbers to be in an individual cell. How do I do it? I think MS Excel 2003 was able to do it. I currently have Excel 2007. Please help. Thank you very much. EggHeadCafe - Software Developer Portal of Choice A Wrapper for the Dispatcher class of Threading Namespace to manage thread items http://www.eggheadcafe.com/tutorials/aspnet/bce7889e-d2cf-42b8-a6af-2f01a383cff6/a-wrapper-for-the-dispatc.aspx Hi, You may use Data >...

cell in cell formulation possible???
Let's say I have a string of text in a cell, something like: "We may charge you $XXX fee if item is not returned in good condition." Is it possible to make that XXX a formula/calculation (based upon information entered in another cell)? ="We may charge you $" & format(A1,"#,##0.00") & " fee if item is not returned in good condition." HTH, Gary Brown "Abi" wrote: > Let's say I have a string of text in a cell, something like: > > "We may charge you $XXX fee if item is not returned in good condition." &g...

Do Not Want to count blank Cells
I copy pasted an Access dynaset (e.g., results of a crosstab query) into an Excel spreadsheet. I tried to use the function =counta(a4:z4) to count the number of entries (nonblank) in row 4. But the result is giving me 26 because it is counting blank cells. Is there a way to count the entries without counting the blanks or a way to easily reformat or change the blank cells so the formula will not count the blanks. I do not want to have to manually delete every blank cell in a table 26 columns by 2000 rows. Thank you, Steve CountA does what you want. It seems that you must have s...

How to matching substring from a Cell
Dear Sir/Madam, I am now having a column likes below Column A My Name is David Chan Marry David Williams He is David and is a boy I want to find out Column A cells with contact the word "David', then display "T" on Coloum B if found or "F" is not found. The result should be as following: Column A Column B My Name is David Chan T Marry F David Williams T He is David and is a boy T How can I do it ? I have tried if( ), but it only match the c...

moving cells
I have a roster with 30 names on a fixed schedule for 15 weeks i want to move the names through the schedule. Thanks Hi Paul, I would try an Excel web search on your Google toolbar or at http://www.google.com/advanced_search?hl=en for excel rotation OR rota schedule OR timesheets and a Google Groups search on a dropdown on your Google toolbar or at http://groups.google.com/grphp?hl=en&tab=wg&q= rotation OR rota schedule OR timesheets group:*excel* to get you started then ask specific questions if that does not provide enough information. --- HTH, D...

Attaching Comment to cell contents rather than cell
I have a spreadsheet that is regularly upadated overwriting columns, however I need to enter comments on the sheet referenced on the content. The next time I update the sheet the referenced cell will move down a row, but I need the comment to stay with the content rather than the cell. Any suggestions? Many thanks If the values that you're commenting are unique, you may want to toss the comments and create a table in another worksheet. Value Comment Then use an =vlookup() formula to return the "comment" to the adjacent cell. El Damo wrote: > > I have a spreadsh...

display on first occurrence of a change in the value
How can I have a report display a text message upon the first occurence of a change in the value of a field in the underlying table? In other words, the field in this table has the same value for many records in a row, but all of a sudden that can change and I want to alert the report reader once that subsequent records are now reflecting these changed values. What I think I need is some kind of switch that changes from say 0 to 1 when that first change happens. Thanks. Alan adgon, Use the Hide Duplicates propety set to Yes. Only the first instance of each value will display. --...

changing settings: comma into point
hi there! currently, my excel is set to have a comma as a delimiter. a is want to copy-paste point-delimited data into excel, i have t change the excel-settings i guess? does anybody know where i can fin this option? denni ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Hi Dennis, > hi there! currently, my excel is set to have a comma as a delimiter. as > is want to copy-paste point-delimited data into excel, i have to > change the excel-settings i guess? doe...

An issue regarding the formula #2
Hi, It's OK to copy the formula from one cell to another one within the same sheet. How about that to copy the formula from one Excel file to another one? It seems only the value of that cell will be copied to that file but not the formula inside it. I even have tried to copy the corr. formula within that again but just the copied formula will not work successfully in there. -- HuaMin ...

Pause and Input to cell
Help, Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, then the macro would place the input data in a cell, let's say d9. How do you do this in Excel. Val, InputBox. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Val Steed" <vals@msn.com> wrote in message news:uEGWBOXTEHA.1168@TK2MSFTNGP11.phx.gbl... > Help, > > Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, > then the macro would place the input data in a cell, let's say d9. > >...

Can a cell be turned on or off?
A1=B1*C1+D1 A2=B2*C2+D2 A3=B3*C3+D3 A4=B4*B4+B4 I would like to be able the have the total in any one of the A cells to add up only if the cell is click on. In other words, can a cell be turned off or on as needed? -- Joe Right click sheet tab>view code>insert this Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column <> 1 Then Exit Sub 'B1*C1+D1 tr = Target.Row Target.Value = Cells(tr, 2) * _ (Cells(tr, 3) + Cells(tr, 4)) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@g...

Problem when I changed my category to "Payment for Invoice"
I'm using "Microsoft Money 2005 Small Business Edition" Anyone has ever experienced this? I sold a product to my customer and created an invoice. He transferred me cash to one of my accounts but I had not realized what that transfer was for. I then treated it as liability I owed him. When I knew what that trasfer was, I change that transfer to "Payment for Invoice". MS05 SB then asked me to allocate that amount to the invoice I created (same amount). I accepted and it went through with no problem. However, when I checked at my account list, the account receivable wa...

Change Default View for Advanced Find Results? (OL 2003)
I'll pose the brief question first. If you know the answer to it, there is no need to read the background (and many thanks in advance!). The question: Is there a way to configure Outlook 2003 Advanced Find so that the results always come up with a user pre-defined view (set of fields) rather than Advanced Find results' default columns? (Or, better yet, is there a way to permanently reset standard Find to search only From and Subect instead of fulltext?) Background: I liked the fact that standard Find in OL 97 defaulted to searching >From and Subject...took less time than fulltex...

How do identify a blank cell in a formula
Example: IF(s69=blank,"Void".... What I want to say is if a cell, (s69in this example) is blank, enter the word "Void" but the above way don't work - I don't know how to enter that in this IF formula. Barb, here is one way =IF(ISBLANK(S69),"Void","") -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Barb123" <Barb123@discussions.microsoft.com> wrote in message news:47...