Linking/referencing the next column in a different excel sheet

[kidnos]

Hi all, I am having trouble trying to figure this out.
I have cell A1 in sheet 1 which contains data going through column BA1. I have another sheet called sheet 2 that I have linked to cell A1 in sheet 1. Within sheet 2 I would like to be able to link to the other columns in sheet 1 with without dragging or filling the cell reference right. I tried messing around with offset but I dont think its going to work. Simply put, I would like cell B1 in sheet 2 equal to cell A1 + 1 column to the right of sheet 1 (without right filling). Thank you

 

[SkipVought]

Hi,

You could try thinking real hard, but that never worked too well for me as far as actually getting something done.

What's the problem with, "dragging or filling the cell reference right?" I do similar tasks almost daily, as well as other things that take a bit of effort, some even more than the task you have described.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kidnos]

Ok, I deserved that response...
There is no problem. I dont doubt your or my ability to drag and fill right. I was actually trying to limit the amount of user interaction within the sheet. I just didnt want someone to forget to fill all the way and then end up with an incorrect number. A simple text box saying
dont forget to fill right" would suffice but why not test the knowledge of Sarcastic Skip?

 

[xlbo]

Not sure what you mean by not filling to the right as whatever formula you choose will need to be filled/dragged somehow. Only way around that is to use VBA and hook into either the change or select events of the worksheet.

Anyway, for your Offset question, this is how you might use it on sheet 2 where the cell reference stays static but the cells it returns are dynamic:

=OFFSET(Sheet1!$A1,0,COLUMN()-1,1,1)

in Sheet2 A1 would return sheet1 A1. In sheet2 B1 would return sheet1 B1 etc etc

I guess your 2nd response is what Skip was getting at - give us the actual problem rather than what you think the solution to the problem might be.....you may get better answers...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

" I just didnt want someone to forget to fill ..."

You failed to state the reason for the requirement. Totally different issue that your workbook is being used by others.

Your sheet seems to be designed, from what I can gather, as a pivot report: 1) data elements down the left-hand side, 2) other data elements across the top and 3) agregations (forumlas) in the middle. YES?

If so, then your data collection ought to be in a different sheet/table with a known number of fixed columns. Then your data table can simply be used to create the kind of table that you or your manager want to see: a Pivot Table Summary Report. That way, there is no ambiguity about filling formulas for the users!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kidnos]

Understood, I think I have it. Thank you.