Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
modify all formula references in multiple cells
I currently have several cells in a summary worksheet that reference values
in the cells of a data worksheet (='sheet#'!B3). This works well. I now have another summary worksheet that I want to reference the adjoining cells of this same data worksheet. I can easily copy and paste all of the currently referenced values from the summary worsheet to the new summary worksheet, but I would like to know if there is a easy way to now change all the copied referenced cell values (in this new summary worksheet) to now reference the cells in the adjacent column just to the right of the data worsksheet? Example Current copied value: (='sheet#'!B3) Desired value: (='sheet#'!C3) I need to do this several cells. Thank you in advance for your assitance Bengineer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
modify all formula references in multiple cells
try this
Assume A1 is the source cell, and you want to copy the same formula starting from cell B1 and down in the column Select your cells using Ctrl Go to the first cell, B1 type = click on the source cell, ie A1 press Ctrl and Enter -- Hope this is helpful Appreciate that you provide your feedback by clicking the Yes button below if this post have helped you. Thank You cheers, francis "Bengineer" wrote: I currently have several cells in a summary worksheet that reference values in the cells of a data worksheet (='sheet#'!B3). This works well. I now have another summary worksheet that I want to reference the adjoining cells of this same data worksheet. I can easily copy and paste all of the currently referenced values from the summary worsheet to the new summary worksheet, but I would like to know if there is a easy way to now change all the copied referenced cell values (in this new summary worksheet) to now reference the cells in the adjacent column just to the right of the data worsksheet? Example Current copied value: (='sheet#'!B3) Desired value: (='sheet#'!C3) I need to do this several cells. Thank you in advance for your assitance Bengineer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
modify all formula references in multiple cells
The suggested method assumes that all of the sourced cells are in adjacent
rows. Not in my case. So this method would be very labor intensive. Please reread my post. My worksheet with data is established. My summary worksheet has over a hundred entries that retrieves specific data (from various rows) within this data worksheet by using the following method in its cells (='sheet#'!B3). Note this is just one cell in the summary worksheet, each cell is unique. On a new and different seperate summary worksheet, I now wish to collect the data that is in the adjacent column of the data worksheet. I am looking for a way (maybe a macro?) to have my entries in the new summary worksheet converted from (='sheet#'!B3) to (='sheet#'!C3) and (='sheet#'!B16) to (='sheet#'!C16) and so on. Bengineer "xlmate" wrote: try this Assume A1 is the source cell, and you want to copy the same formula starting from cell B1 and down in the column Select your cells using Ctrl Go to the first cell, B1 type = click on the source cell, ie A1 press Ctrl and Enter -- Hope this is helpful Appreciate that you provide your feedback by clicking the Yes button below if this post have helped you. Thank You cheers, francis "Bengineer" wrote: I currently have several cells in a summary worksheet that reference values in the cells of a data worksheet (='sheet#'!B3). This works well. I now have another summary worksheet that I want to reference the adjoining cells of this same data worksheet. I can easily copy and paste all of the currently referenced values from the summary worsheet to the new summary worksheet, but I would like to know if there is a easy way to now change all the copied referenced cell values (in this new summary worksheet) to now reference the cells in the adjacent column just to the right of the data worsksheet? Example Current copied value: (='sheet#'!B3) Desired value: (='sheet#'!C3) I need to do this several cells. Thank you in advance for your assitance Bengineer |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
modify all formula references in multiple cells
I discovered the following solution.
Copy and paste all reference values to a new spreadsheet. One example of one reference is:(='sheet#'!B3). Now select all the pasted cells in a single column. Then drag bottom right corner of lowest cell within the selected cells into the empty column to the right. Excel will populate this new empty column with all new reference values and these values will be adjusted to now be the adjacent column to the right of the same target worksheet. One example of the new adjusted value: (='sheet#'!B4). Now you can either delete the original pasted column or replace the contents of the pasted column with the contents of the newly created column. This method was especially helpful when the references values in the column were not refering to consecutive rows in the target worksheet. "Bengineer" wrote: The suggested method assumes that all of the sourced cells are in adjacent rows. Not in my case. So this method would be very labor intensive. Please reread my post. My worksheet with data is established. My summary worksheet has over a hundred entries that retrieves specific data (from various rows) within this data worksheet by using the following method in its cells (='sheet#'!B3). Note this is just one cell in the summary worksheet, each cell is unique. On a new and different seperate summary worksheet, I now wish to collect the data that is in the adjacent column of the data worksheet. I am looking for a way (maybe a macro?) to have my entries in the new summary worksheet converted from (='sheet#'!B3) to (='sheet#'!C3) and (='sheet#'!B16) to (='sheet#'!C16) and so on. Bengineer "xlmate" wrote: try this Assume A1 is the source cell, and you want to copy the same formula starting from cell B1 and down in the column Select your cells using Ctrl Go to the first cell, B1 type = click on the source cell, ie A1 press Ctrl and Enter -- Hope this is helpful Appreciate that you provide your feedback by clicking the Yes button below if this post have helped you. Thank You cheers, francis "Bengineer" wrote: I currently have several cells in a summary worksheet that reference values in the cells of a data worksheet (='sheet#'!B3). This works well. I now have another summary worksheet that I want to reference the adjoining cells of this same data worksheet. I can easily copy and paste all of the currently referenced values from the summary worsheet to the new summary worksheet, but I would like to know if there is a easy way to now change all the copied referenced cell values (in this new summary worksheet) to now reference the cells in the adjacent column just to the right of the data worsksheet? Example Current copied value: (='sheet#'!B3) Desired value: (='sheet#'!C3) I need to do this several cells. Thank you in advance for your assitance Bengineer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Inserting Cells and Formula References | Excel Discussion (Misc queries) | |||
Inserting Cells and Formula References | Excel Worksheet Functions | |||
formula references vertical cells in a separate worksheet | Excel Worksheet Functions | |||
copy cells with formula without references | Excel Worksheet Functions |