I construct speadsheets using formulas that contain both relative and mixed cell references. When I have completed a worksheet, I need to change all of the formulas to have all absolute cell references. I must do this in order to be able to filter and sort properly.
How do I change all formulas at one time to absolute without having to select each individual cell one at a time and toggle using the F4 key to change the reference?
thanks
I've been trying to do this as independently as possible but this one is for the experts....
In Sheet "Today" there is a column (AH) with 1-50 cell references such as "TaskList!$Q$25" generated by an Address() formula. (number of refs will vary but only the row# will change).
In col. AE there are corresponding item numbers for these cell refs which are each unique but not in any order. (you'll see that they correspond to the row numb in the ref).
AF & AG are information that I want to copy and paste(values) into Sheet "TaskList"
Code:
row AE AF AG AH
21 16 TRUE canc TaskList!$Q$25
22 12 TRUE canc TaskList!$Q$21
23 2 TRUE 4/6/10 TaskList!$Q$11
24 18 TRUE 4/6/10 TaskList!$Q$27
25 14 TRUE canc TaskList!$Q$23
26 4 TRUE 4/6/10 TaskList!$Q$13
27
I would like to develop a marco that would
--1) Determine the number of references in AH (?)
--2) in sheet "TaskList", paste(value) of AF/AG (above) into col. Q&R using the cell refs.
Code:
row H I J K L M N O P Q R
10 1 4/3/10 4/3/10 0 1:30 4 1 4
11 2 4/3/10 4/7/10 2:00 4 4 6 TRUE 4/6/10
12 3 4/3/10 4/7/10 4 4:00 4 1 2
13 4 4/3/10 4/6/10 1:00 3 5 6 TRUE 4/6/10
14 5 4/3/10 4/11/10 8 1:00 2 9 4
15 6 4/3/10 4/11/10 0:30 1 2 3
16 7 4/3/10 4/11/10 8 0:30 2 6 3
17 8 4/3/10 4/11/10 8 1:30 2 5 8
18 9 4/3/10 4/11/10 8 1:30 2 3 3
19 10 4/3/10 4/11/10 1:00 2 5 2
20 11 4/3/10 4/11/10 9 2:00 2 8 7
21 12 4/6/10 4/7/10 1 3:00 2 9 9 TRUE canc
22 13 4/6/10 4/7/10 1 2:30 2 7 5
23 14 4/6/10 4/6/10 0 1:00 3 9 0 TRUE canc
24 15 4/6/10 4/6/10 0:30 3 3 2
25 16 4/6/10 4/10/10 4 1:30 3 2 8 TRUE canc
26 17 4/6/10 4/6/10 0 4:00 3 3 8
27 18 4/6/10 4/6/10 0 1:00 2 8 4 TRUE 4/6/10
You guys really amaze me and I really try to figure this stuff out on my own but this is beyond me! So big thanks!!
+adam
Good morning,
I am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:
=MIN(RuleofOriginData!AR$4:AR$63)
=MIN(RuleofOriginData!AS$4:AS$63)
=MIN(RuleofOriginData!AT$4:AT$63)
However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?
Thank you
Hi - Newbie here.. (can hear the groans already
I am trying to use a macro to copy a set of cells.
I have successfuly created a macro - the problem is I would like the column reference to be absolute but the row reference to be relative. I can have one or the other but not mixed it seems.
If I have not explained myself well, perhaps this may help - I need to copy cells $J1:$S1 and paste them to $L1:$U1
Currently the macros works if the cursor is in cell J1 when I activate the macro but if I move the cursor to any other cell it copies and pastes the wrong set of cells.
Any help you can give is much appreciated.
Thanks
Chris
I am working on a spreadsheet that has dates down column A and stock symbols across row 1. For each cell, I need to make calculations that reference both the symbol and the date the cell corresponds to. I have gotten the first column down fine; but when I start moving to columns 2 through infinity, I'm not sure how to get the references to stay accurate within the VBA code. Any suggestions?
Thank you!
Hi All,
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
I would be very grateful for your help.
In a spreadsheet I am working on, the master data is contained in a
spreadsheet with data reported in rows. A client has asked us for various
reports, but wants the data reported in columns. In order to create a link
to the master spreadsheet, I used a mixed reference, to have the absolute
row, but a relative column. However, when I try to copy this formula
forward, it only copies the same mixed address, rather than advancing the
column as I thought it would.
I tried to do that but it will not work in the actual cell reference.
I'm stumped.
humejap Wrote:
> Have you tried the formula
>
> =Concatenate("$J$",H1)
>
>
> --
> humejap
> ------------------------------------------------------------------------
> humejap's Profile:
> http://www.excelforum.com/member.php...fo&userid=5506
> View this thread:
> http://www.excelforum.com/showthread...hreadid=395098
--
jhockstr
Hello All,
I need to use a number that was calculated from a formula in a cell
reference. Here is the situation: I have a large spreadsheet that
changes periodically. I need to count the number of items in that
spreadsheet(i will use COUNTA - already works) and then take that
number and use it as a row number in a cell reference like this:
In cell H1: =COUNTA(A2:A9999)
Then take that number in cell H1 and insert that into say $J$XXXX where
XXXX represents the number in cell H1.
I'm looking to increase the calculation speed of the workbook by only
calculating cells that I need to.
Thanks for the help,
Jason
--
jhockstr
Hi - I'm new here. I've looked over a few pages of posts but didn't find a topic I thought would address what I would like to do.
Suppose I have the numbers 1-10 in cells A1-A10 respectively
suppose in cell A12 I have a value x and in cell A13 I have a value y.
I would like to put a formula in cell A15 that sums up the values in cells Ax to Ay. For example, if the value in cell A12 is 4 and the value in cell A13 is 6, the result of the formula in cell A15 would be the sum of A4,A5,A6 = 15.
I have had no luck coming up with a formula that does this, or in searching for a formula that does this. A little help, please?
Thanks,
Peter
