Thread: Question about Excel

Nevermind!! I found the answer! I can use the INDIRECT function.

Thanks anyway.

Anyone know how to make the UNDO and REDO Buttons NOT shift the display back the the cell being changed? I have a cell near the top of a spreadsheet that contains my assumed TSP return. This number affects many calculations throughout the sheet. If I am looking further down at projected income at some age, for example, I want to toggle back and forth among various input values and see the results. It is most annoying that every time I hit either undo or redo the view flies back to the top, to the cell I changed. I want the view to stay where I put it and let me just look at the results. Any ideas?

@ Pilgrim

Maybe freeze the window and then just manually change the input?
Window --> Freeze Pane

Originally Posted by fabijo

I've been struggling with a problem that I just cannot find the answer to. Maybe someone here knows what I am trying to do. Let's see if I can explain my problem. Let's say I have a column of data that has 100 separate values in them. They go from A1:A100. I could easily go to another cell and put =sum(A1:A100) and that will give me the sum of all those cells. Now let's say I have a cell (Let me call it B1) that I can put in a number from 1 to 100. If I put 65 in that cell, I would like a cell (Let's call that cell C1) that takes that number and sums up that many cells from column A. It would be like saying =sum(A1:A65).

To recap:

Cells A1 to A100 have numbers in them.
Cell B1 has a user entered number from 1 to 100
Cell C1 returns the sum of all cells from A1 to A(# in B1)

I have no idea how to get this accomplished. I've been messing around and searching through Excel help and Google, but I just can't find the answer. Let me know if what I even said makes any sense.

Wouldnt it looks something like this:
Column A has the data set from A1:A100
B1 has the user inputted number between 1 and 100.
C1 would be something like =sum(A1:indirect("A"&B1))

It works. Check out the attached spreadsheet.

Originally Posted by ChemEng

@ Pilgrim

Maybe freeze the window and then just manually change the input?
Window --> Freeze Pane

No, the panes are already frozen to keep column headings in place as I move around.

You may already know this: but if you select a cell when you hit the freeze panes, it will freeze the rows above it and the columns to the left of it. I didnt realize it could do both when I first learned about freezing panels until later.

Another option is to point the input to a another cell in the area that you can change and change it from there. If the control cell is B1 and you are working down in AQ456 area, make B1=AQ455 or somesuch and then change that cell. It would be a quick fix.