Excel: fixed cell reference 1

[DajOne]

Two questions regarding the same overall problem.. How to display fixed cells despite having insertions done or select the cells through a menu..

In Worksheet One, the user inserts through a macro colums B,C,and D at the insertion point is colums E... thus B,C, and D colums become E,F, and G colums, the old E,F, and G colums become H,I, and J colums etc,... the colums are pushed by a number of 3..

In worksheet Two

Column C is suppose to show data from Worksheet One, column F, column E is suppose to show data from Worksheet One Column I.. etc.. regardless if colums have been inserted in Worksheet One.. with making the cell fixed (With $) the forlulas in Worksheet Two are being pushed thus column C (worksheet Two) data reflects data from Worksheet One column I instead of F..

Problem 2.. . Is there a way to select the column to be shown on Worksheet Two with a check box on the Worksheet One colums?

I trued to explain it as best as I could.. IF you think this is not clear enough.. please mention it..

Thanks

 

[SkipVought]

Hi,

The $ in the cell reference only affects what happens when that formula is copied.

If your formula has a reference to $A$1 or A1, and a one column insert is done, your reference will indicate either $B$1 or B1 respectively.

That is what happens when Insert is performed.

You might want to rethink the process.

I don't understand the second problem -- so you have checkboxes for each column? (how many?) you check a box and then what do you want to happen -- all the data ffrom the corresponding column on sheet2 is displayed on sheet1? You see the data as long as the box is checked? What are you trying to accomplish?

Inquiring minds need to know Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DajOne]

Hi Skip,

Assuming that insert is the only way available... Is there a way to fix the forlula that the cell wont 'jump' from A1 to B1? The worksheet project I am on is for financial statement analysis where every year (or quarter) the user will 'insert' a new column (from teh left) to enter the financial statement numbers .. Each group of 3 colums that are inserted contain qualification, numbers and ratios respectively.. From the left, 2002 year will be in DEF, 2001 in HIJ, 2000 in KLM etc...

The worksheet 2 takkes the numerous ratios from worksheet one and presnet them in a presentable format

Leading to question 2, it would be even better is the user could select which year of financial ratios would be showing on the presentation sheet by selecting the year on worsheet one by a checkk box or something similar...

Hope this helps...

 

[GlennUK]

You could use the INDIRECT function, which converts a string to a reference.

Use a formula like this ...

=INDIRECT("Sheet1!R12C"&(C1-1)*3+1,FALSE)

changing the R12 to whatever row is required, and changing the C1-1 to be year offset ( e.g. 2002 is an offset of 0, 2001 would be an offset of 1 etc etc ).

I used a cell ( C1 ), to hold a value chosen by a pull-down, so that the INDIRECT formula brought stuff back according what choice of year I made. You could do something similar.

Glenn.

 

[DajOne]

Thanks Glenn for the tip and idea... I will try variations to see what I can do...

Thanks again