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!

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!

- Need help with a small formula and referencing cells
- I can"t paste / drag formula from one cell to another
- Dragging formulas down rows while referencing across column
- Drag down range formula but skip 4 cells
- Drag formula but skip every other row
- Drag a formula down one cell, reference cell jumps down four cells?
- Issue with dragging formulas to other cells
- Calculating a weighted average from referencing formulas in other cells
- Drag/Fill Formula & Skip Some Row References
- Dragging formula to adjacent cells issue
- Dragging formulas to cells
- Ignore blank referenced cells in a formula?
- How can I drag formulas with other cell references
- Bold text of referenced cell show in formula cell
- Excel formula timestamp for a data entry of a referenced cell
- Drag formula based on pivot table cell
- How can I drag formulas with other cell references
- Formulas referencing cells stopped working
- Dragging Formulas to Adjacent Cells
- Drag random Cell formula to other cells
- Macro for dragging formulas a variable amount of columns
- Dragging Down Formulas with Constant Cells
- Highlighting referenced cells within a formula
- Excel formula timestamp for a data entry of a referenced cell

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

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.

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!

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.

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

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.

Thanks.

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.

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

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?

Thank you!

('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?

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.

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.

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

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.

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 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 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

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

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!

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

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

No luck finding an answer? You could always try Google.