modify all formula references in multiple cells

[Bengineer]

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

 

[xlmate]

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]

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

 

[Bengineer]

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.