I need to copy/paste data into a table. From there, I need several columns to autopopulate with data. It works for simple absolute cell references i.e. Date = $O$1, Period = $O$2. However, when I try to use a formula I get an error and when I use other methods, they don't work. I have also tried...
How do I get a macro to keep the absolute reference of active cells for use in a cell formula later? i.e. I have cell E11 selected, I want my macro to remember the value in cell E11 and the reference, then move to column A in the same row and make a formula that is = 18 - E11 (which should...
Hi There!
Hoping this is pretty simple, I just can't work it out, I know a plain vlookup isn't the answer, nor is using absolute $, I've tried indirect and another couple of options but to no success. The spreadsheet has more info, however for example purposes I have simplified it.
What I'm...
When I run this VBA code:
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[14]C[-1])"
It places the following formula in the active cell:
=SUM(H19:H33)
I need to modify that VBA code so that it whatever the active cell is it makes the first reference absolute.
If I was clicked inside C19 it should...
Hi,
I was hoping someone could help me with the following issue:
I have an input file with conditional formatting helping me to verify input data. However, since people will be able to add lines in the input template, this conditional formatting will be split into many once people add new lines...
I have the following formula in a cell on one work sheet:
{=IF(ROWS($B$4:$B$4)>COUNTIF('Lab Submissions Ad 1'!$N:$N,"upper"),"",INDEX('Lab Submissions Ad 1'!$A:$A,SMALL(IF('Lab Submissions Ad 1'!$N:$N="upper",ROW('Lab Submissions Ad 1'!$N:$N)-ROW('Lab Submissions Ad...
I vaguely recall that there is a way to convert a range of cell references to absolute cell references in an efficient manner, but I have forgotten how.
Basically the problem is that I wish to copy a range of cells with standard cell referencing to another column next to the original column and...
Please excuse the sanitised description. I have a list of people conducting a number of events each, over time. I need to be able to tell the time difference between the last event for each person and the previous events they conducted. I have attached an example of how I can do it manually...
For some reason, the below snippet of code inserts a formula where the lookup value for the match functions actually refers to cell RC1, rather than the first column of the sheet and the row of the active cell. Does anyone know why this code would do this? I think it has something to do with the...
Hi,
I have a spreadsheet that has some values in cells A1:E1 (let's call these base values 1)
The values in cells A2:A10 are calculated using the base values in A1:E1
So, the formulas is A2:A10 might look something like this...
A2 = ($A$1 + $B$1 + $C$1 + $D$1 + $E$1) * 1
A3 = ($A$1 + $B$1 +...
Hello All ~
I am writing a macro to prepare a tab for the next year's data. I have been searching this forum and have found many answers to issues a long the way. I have searched and tried many things but cant get this last part to work correctly.
Right now all the coding that is active...
Anyone know a shortcut or have a macro that will convert a range of cells or a spreadsheet to absolute cell references. I want to avoid using the F4 or Find/Replace functions. Thanks!
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.