Excel Cell Reference: change multiple references simultaneously & uniformly?

Question

Given a multi-page workbook, where I am referencing different rows on page 1:

.....A......B.....C.....D
1:..27....43....19...102
2:..29....55....48...267
etc.

and on page two I'm entering values by pointing at cell references on page 1:

..........A.............B............C
1:...=P1!A3....=P1!C3....=P1!W3
2:...=P1!A7....=P1!C7....=P1!W7
etc.

Is there any way I can enter the references OTHER than by changing them one at a time to point to the correct cells? Each 'pointer' column will point to the same 'pointee' column, and each 'pointer' row will point to the same 'pointee' row - but there are major jumps from pointer row/column to the next (A will be followed by G will be followed by AB. etc.)

Is there some way of (for example) putting a variable in a cell reference? Or some other way where I don't have to change the info for every cell manually?

cherria · Accepted Answer

Not really, there are 2 ways you could do it though.

1. you could put the formulas in that reference all the data on P1
i.e. in A1 put =P1!A1, in B1 put =P1!B1 etc. and then once complete, just delete the entire rows and columns you do not need on P2, this would leave you the result you want.

2.  A better way would be to use the VLOOKUP formula.  Put a key value in column A for each row you want and then use VLOOKUP to scan the table on P1 and pull out the values you want from the rows that match your key value.

Anonymous · Answer

Right click and copy the formula. Then highlight all the cells you want to change, Right click and click paste specal, the check fomulas the ok.

Trending News

Excel Cell Reference: change multiple references simultaneously & uniformly?

2 Answers