Fixed Cell Reference

Mark Proulx

I have search the db for answers but likely have my terminology mixed.  I have a sheet which is populated by clients via a web form.  New entries appear on the 1st line.  I want the value in 'Bears 1' to populate in another sheet or on the same, doesnt matter.  The specific cell is key as this is the most current value.

 

Thoughts?

Mark Proulx

Craig

 

Many thanks for your prompt and detailed response.  I need some time hone my logic skills as I am trying to follow your steps but the logic is escaping me. I will work through and reply if I get stuck.  Cheers

J. Craig Williams

Mark,

 

If you weren't complicating things by add a row with where you want to get the data...

 

then in the same sheet, absolute references are designated by $

 

=$Bears$1

 

will point to both column and row.

 

=$Bears1

 

will point to the Bears column, row 1 but if it is copied, it will be pointing to the relative row position.

 

Same for absolute column.

 

If your cursor is in the cell with the formula but not at the end, Ctrl+L (cmd+L on Mac) will cycle through the options.

 

However, that won't work as once you add the new row, well, the formulas update to

 

=$Bears$2

 

which is good, otherwise most (all?) of our formulas would be messed if we added a row.

 

Links are going to be the same way.

 

HOWEVER, you are ONLY getting new rows from a WebForm (and always at the top) then read on...

(hold your applause until the end, this is pretty cool)

 

1. Make sure your [Created] column is available.

2. On the far left, create a new column. Name it what you want, you can hide it after you test things. I named mine [Test] because I wasn't sure this would work.

3. In the column put

=[Created]1

in row 1 and copy it to at least the first 2 rows, as you want it to auto-populate when you and add new row. You don't need to copy it to the whole column - and see step 5 for a difference in the last row.

4. And a new row at the BOTTOM of your sheet. It should contain a different formula than the =Created## formula. In my example, the new row that I added as row 540.

5, The new formula in the [Test]540 cell should be:

=MAX(Created:Created)

 

6. In the [Bears] column, add this formula:

 

=INDEX(Test:Bears, 1, (MATCH(Test540, Test:Bears) + 5))

 

(modified a bit for your specific sheet)

a. Change the "+ 5" to get the correct column over from the left.

In my sheet, Bears is column 6, so I need to add 5.

If Bears is right next to the new column, then just add 1.

b. Change the Test540 to point to the bottom left cell in your sheet.

 

Some explanation may help:

 

We'll start with the MATCH() function,

 

We are looking for the value in cell in the bottom left -- it will always be there because new rows will be added above it and you won't add columns to the left.

 

The range of interest is the columns from Test (the new one we added) to where the data is (Bears).

 

That match should always return a 1 because the latest row's created data will be the most recent.

 

We add +5 (or what will work in your sheet) to change this 1 to the number of the column where our data is.

 

Now that we know that, we just do an INDEX on the same range with the index numbers 1, 6  (again, yours may be different)

 

And Bob's your uncle.

The cell will allows have the value you want and you can link that out to another sheet if you want.

 

Every new row will auto-populate the Created date we need, and our formula that looks at things doesn't need to change.

 

Note that the LOOKUP feature does not work for this methodology. I'm not sure why.

Also, using the Created date directly does not work. I think that is related to changes they made late last year to the way date/times are handled.

 

Craig

 

 

 

 

J. Craig Williams

Mark,

 

I made another edit.

I'm also going to post this to my webpage and the instructions there may be clearer.

 

Craig

Mark Proulx

Many thanks Craig.  

Mark Proulx

What is the URL to your webpage?

austinurbanSeagate

Hi Craig,

    I wanted to run this solution by you, and see why it may not work in this case (it has been working for me, and seems simpler)...

1) Add a column (wherever the user wants)

2) Add a question to the form, set the default fill to "1" and label the description "DO NOT CHANGE" or something to that effect

note: these steps could be done with a question on the form which is always filled in the same way, doesn't have to be a "1"



3) Fill the summary cell or desired cell with the formula:



=INDEX(COLLECT([Desired Column]:[Desired Column], [New Consistent Column]:[New Consistent Column], 1), 1)

 

4) then it will always fill with the first index in the data collected, which should be the most recent form submission data in column [Desired Column].



There may be a downside to the way I've done it, I just can't see it...

-Austin