Free Microsoft Excel 2013 Quick Reference

Dragging Formulas - Skipping Referenced Cells

Hi guys,

I've got a slight problem I need help with. To explain it better, let's say I have the following data across a number of columns on one worksheet:

WORKSHEET1:
24 16 45 12 10 76 13 2 57 76 34 26 11 9 8 22 13 7

In simple terms I want to take some of those same values and place them in a different table on a different worksheet. In my case it's the 8th value along I want (in red). So ideally my new table would look like this:

WORKSHEET2:
24 57 13

Ideally I want an easy way to drag the formula across the cell and have it automatically populate the cells as above. At the moment all it does is re-create my original table. I have already tried selecting a number of cells before I drag so it knows what I want to do but this doesn't work either and it seems to have a mind of it's own taking data from completely different columns scattered about the place. It doesn't neccesarily have to involve dragging as long as I can easily populate these cells without having to manually go to the other worksheet to select the correct cells.

Any help is much appreciated! My boss will be very happy if I can help him solve this!


Hi,

I need some help with formulas and referencing cells in Excel. I've used Excel before for simple tasks like finding the sum of number etc. but I've never used it for anything more complicated than that.

I've attached the file I'm working on.

In there, is a worksheet called "Clients", and a worksheet called "Form". (I'll format the form later Sheet "Clients" contains all the data, and sheet "Form" is where all the referencing happens.

I've created a dropdown list in sheet "Form", that takes its data from sheet "Clients". When i select "Business A" in sheet "Form", all data related to "Business A' which is stored in a row in sheet "Clients", needs to appear in sheet "Form".

My problem is, I've managed to do it for "Business A", but when I choose "Business B" how do I tell Excel to look for data in cells B3 C3 D3 and not B2 C2 D2. Its hard to explain here.

I'm sure you'll understand what i'm trying to do if you looked at the Excel file and selected Business B and Business C from the dropdown list. There might be some method like using * (wild cards) to reference cell numbers.

I did a formula =IF(B1="Business A",Clients!C2,"Error") with a lot of difficulty.

Also, is it possible to use an easier reference instead of "Business A" "Business B" in the formula. (Only in the formula and not in the drop down list)

Thanks
Norman

I'm using excel 2007. Today I opened my excel to continue my work.
But when I copy/paste the cell with formula to another cell, it didn't copy the formula,
only the value. I tried dragging it down to the next row but only the value was pasted.
It didn't auto calculate. I check the calculations options, automatic is check-marked.

And not only the formula, it also won't copy the boarders, the styles, the fonts, etc.

I don't know what happened. Please help.

Hi,
I've searched extensively on this sight and all over to find how to create a formula that can be copied down multiple rows while referencing cells in another worksheet that have a relative cell value (in a pattern). For Example:

Sheet one has values of 1, 2, 3 in c20,f20,I20 respectively
(skips over 3 columns a time)

Sheet two: A1 = sheet one, c20 (in this case "1")

A2 = sheet one, f20 ("2")

A3 ...

I need to creat a formula for a1 that would allow me to drag down and keep the referenced values the same. So for every row down when I drag, then it would need to add 3 columns to the referenced cells. This would save tremendous time in manually referencing thousands of cells, ie I could just drag down one time. Any help would be greatly appreciated. Thanks!

How can I drag the following forumla but skip 4 cells in between, for example:

J4 = MCONCAT('Sheet1'!U5:U29,",") (The comma is to be inserted b/w value of each cell)
J5 = MCONCAT('Sheet1'!U34:U59,",")

Is there a way I can drag down this formula and be able to skip the 4 cells (U30 to U33) in between?

I tried the following offset function, but I am not sure where and how to insert the comma (,) feature:

J4 = MCONCAT(OFFSET('Sheet1'!$U$5,(ROWS(J$4:J4)-1)*29,0,25))

This one works perfectly fine when I drag down, but there seems to be no way of inserting comma (') in between the value of the cells.

Does any one have a better suggestion?

Thanks.

Hi,

I am trying to link the cells up in a new workbook the problem is the data I need is setup in a way the formula needs to skip every other row.
For example:

When I drag this the next cell down is "C70" 
However I need the formula to be C71 and so on down to C137

How can you drag a formula yet skip every other row?

Any Idea's?

Thank You, Mike

I am created a table that references the exact values of cells in a different table. I have to drag this formula down 100 cells, so it will be tedious to input these manually.

Example:

New table created in column P & Q, referencing data in a table in columns A through N.

P39: '=B41' Q39: '=B43'
P40: '=B45' Q40: '=B47'
P41: '=B49' Q41: '=B51'

Thanks in advance.

I have ranked a value of a cell (S14)with this formula =RANK(S14,S14:S18,0), I also want to rank the rest of the referenced area, but when i drag the formula to th S15 to S18, the referenced area changes along with the cell being ranked. Is there a way to drag the formula and only have the cell being ranked change?

Thanks.

Is there a way to calculate a weighted average referencing formulas in other cells? For example, in Sheet 1, cells A1:A10 all have the number 1. Column B is empty. Cell C1 has a formula stating 100% of the number 1's are in column A. Sheet 2 has the number 1 in cells A1 & B2 so the value of C1 is 50%. In Sheet 3, how can I compute the averages of the data of cells C1 from Sheets 1 & 2 without averaging the answer? Averaging the data would say 92% of the 1's are in the A columns, but averaging the values of C1 would say 75%.

Note that more data in columns A & B is routinely added & the formula references in C1 are changed as different ranges of data is to be reviewed. I'd like to be able to change my formula references in C1 on Sheets 1 & 2 & have the changes automatically applied in Sheet 3 if possible.

I have the following formulae in seperate cells were A1, A2 and A3 are the cells

A1 =MAX(Data!C2:C3)
A2 =MAX(Data!C4:C5)
A3 =MAX(Data!C6:C7)

I would like to drag the cells so that automatically the cells below A3 are updated as follows

A4 =MAX(Data!C8:C9)
A5 =MAX(Data!C10:C11)
etc up to 600 rows

Unfortunately it is not being updated in that order. Do you have any suggestions how it can be done.

Regards,

bugi66

I have some formulas that I need to reproduce for new cells from time to time but excel seems to force me to do it manually every time.

If I want to take this formula:
Code:
And drag it down one row it should be:
Code:
What excel actually does is this:
Code:
I need some of the cell references to change but not others. Is there a way to make this happen or do I just have to go back
and correct all the formulas every time?

This is probably a very simple question but I can't figure it out. I am doing a chart for several of our offices and some people are not literate in Excel so it needs to be very simple. They are required to fill in monthly income values which are then used in various calculations. When I drag the formulas to other cells, I get things like #DIV/0! in the blank spaces until the data is entered. How can I get this to show a blank cell, but have the formula be there waiting until a figure is entered?
Thank you!

I am creating a formula for a weighted average. The values in the formula are references of cells in other sheets in the same document. I would like to calculate this average, but ignore referenced cells that are blank. Here is an example of what I am trying to do:

('sheet1'!Q5+'sheet2'!Q5+'sheet3'!Q5)/('sheet1'!R5+'sheet2'!R5+'sheet3'!R5)

Cells 'sheet1'!Q5 and 'sheet1'!R5 are empty, and I would like the formula to ignore these cells so it computes properly. These cells must be included in the formula because they will be populated with data later.

How do I write a formula so that all referenced cells are included but are ignored if the values are empty?

example
cell A1 contains formula "=stdev(a2:a7)
cell a2 contains formula "=stdev(a8:a13)
Is it possible to drag the formula down so that cell a3 contains
"=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5,
a6, a7 etc. etc.

I have a VLookup formula to another worksheet, but I need the bold text of
the referenced cells to show up as bold in the formula cell.

I understand you can do that with Conditional formating if the data is in
the same worksheet, but my data is in a different worksheet and it gives me
an error.

I would like to make a cell display the date/time of a referenced cell when
it has been updated/data changed. The formula =IF($A1>0,NOW(),"") for
example, updates all similar formulas in the entire worksheet (entire column
when autofilled). Does Microsoft make a timestamp for the data entry of a
referenced cell? ...or could you guys please consider putting one in a later
excel version.
Thank you!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

When I create a formula based on a cell in a pivot table I get the correct
answer. When I drag this formul down or accross, the result only relates to
the original cell. It does not drag from the next cell down or accross.

example
cell A1 contains formula "=stdev(a2:a7)
cell a2 contains formula "=stdev(a8:a13)
Is it possible to drag the formula down so that cell a3 contains
"=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5,
a6, a7 etc. etc.

I made a spread sheet last year that used various COUNTIF formulas that referenced other cells. =COUNTIF(I40:ER40,"A") It worked perfect.
I copied that spreadsheet to use for this year. I cannot get this formula to work. I have reentered in various locations and forms, but to no avail. If I enter the data and then enter the formula, it works. However, if I enter the formula first and then enter the data (which I will do over the course of the year) it does not work. I even tried to reconstruct all of this in a brand new spread sheet and it still is not working, although last year's spread sheet still works.
I am using Excel 2003
Any ideas?

I have some data in a spreadsheet that I'm trying to manipulate and I'm not sure if what I want to do is possible.

I have data in B2: D2 and I have data in E2:E4. So one set of data goes across the columns and the other goes down the rows. I want to subtract these two sets of data. I click on a new cell (B21) and put =B2-E2. That works fine. However when I drag the formula to populate the cells is where my problem starts.

I want to drag the formula across the columns to populate B21:P21. The problem is that when I drag across the columns B2 increments to C2, D2 etc...I would like it to instead increment to B3, B4 etc...

Is is possible to drag a formula in one direction (whether across the page or down the page) and have the cells used in the formula increment in the opposite direction?

I hope that was explained well enough for someone to figure out what I mean. I attached a file as well so you might be able to see what I'm talking about. Thanks

Hi

I am new to Excel VBA programmig so need some help.
I am making a sheet that has many colums of data that I want to sumup colum wise. I am doing is using this macro ..
=============================================
Sub Add_Totals()

    For Each NumRange In Columns("C").SpecialCells(xlConstants, xlNumbers).Areas
    
        SumAddr = NumRange.Address(False, False)
        NumRange.Offset(NumRange.Count, 0).Resize(1, 1).Formula = "=SUM(" & SumAddr & ")"
        NumRange.Offset(NumRange.Count, -1).Resize(1, 1).Value = "TOTAL="
        
    Next NumRange
    
End Sub
=============================================
This macro works well for a particular colum and decides the length of the row based on the available data Thus SUM formula is defined by this macro at random cell (for example at C45).
Now I want to drag this Sum formula at C45 (random cell ) toward right side cells i.e. D45,E45 and so on using a MACRO only.

I can imagine the solution but am not able to code it at this stage. Actually macro should be able to recognize C45 and then should drag the formula to
D45,E45 .......

So requirement is to recognize this random cell (that has sum formula defined by the above macro) and drag it to some cell on its right so that other colums also show the total of their values. I also want to make this entire total row in BOLD font.

I will highly appreciate help on this.

Many Thanks

Sam Grogan

I am trying to minimize the number of cells that are not referencing anything. I would like to write a macro that will drag formulas or copy and paste them into a specified number of columns, that is variable. For instance, I have formulas in C9:C35 the amount of columns to be filled in with the formulas changes based on a COUNTIF function. I need to be able to have 50 columns with formulas if the COUNTIF (counting rows on another sheet) is 50 and 75 columns if the COUNTIF is 75 to always mirror what the COUNTIF number is. So far I have only been able to copy and paste the formula 50 columns away with a macro. Help would be greatly appreciated.

Is there any way to use the cross in the bottom right corner of each cell to drag a formula with some cells that vary, but others that remain constant?

For example:
If I want the formula to be:
B2+C2
B3+C2
B4+C2
B5+C2, etc.

Is there a way to drag "B2+C2" down to make it look like the list above, rather than dragging it down to see:
B2+C2
B3+C3
B4+C4
B5+C5, etc.

Thank you!

Hi,

Does any one know how I can use VBA so that when I select a cell with a formula in it, vba will highlight the cells referenced within that formula? Similar to when editing the formula excel will put the coloured boxes around referenced cells.

e.g.

In cell A3 I have the formula "=A2+A1". When I select A3 as the active cell I want VBA to highlight (cell colour) cells A2 and A1 as red.

Cheers

Jonny

I would like to make a cell display the date/time of a referenced cell when
it has been updated/data changed. The formula =IF($A1>0,NOW(),"") for
example, updates all similar formulas in the entire worksheet (entire column
when autofilled). Does Microsoft make a timestamp for the data entry of a
referenced cell? ...or could you guys please consider putting one in a later
excel version.
Thank you!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions