Relative relationship to current cell (with formula)

[mainemojo]

I have a cell H24 with the following formula:

Please Login or Register  to view this content.

Is it possible instead of saying B24, to say something like "6 columns to the left" or would that impact the indirect statement?

[xlnitwit]

Hi,
That's basically what B24 means in a formula in H24. Are you having an issue with it?

[Zer0Cool]

Is it possible instead of saying B24, to say something like "6 columns to the left" or would that impact the indirect statement?

Pardon me if this is something you already know but it sounds like you arent totally clear on relative vs absolute references.

A reference is made of 2 basic parts, the obvious being column letter and row number, ie: B24. There is however 4 states a reference can be in, represented by dollar signs in specific places in a reference:

B24 = relative reference
$B24 = absolute column, relative row
B$24 = relative column, absolute row
$B$24 = absolute reference

So whats the meaning? Typically references are relative, meaning as the formula gets filled up/down/left/right from its origin, the reference changes relative to the origin too. In other words putting =D4 into C3 and then filling or copying that formula into B2 would result in the relative change of the formula in B2 =C3, having adjusted the reference up a row and over to the left a column as our "new" formula is also up 1 row and over to the left 1 column from where we originally got it from.

This is a great benefit most of the time, but sometimes what you referenced in a formula shouldnt change when the formula is filled. The $ sign "anchors" the part of the reference to its right so that it doesnt change. In other words it makes it absolute. So a fully absolute reference doesnt change at all. As seen in my list above you can anchor only the column or only the row as well.

So to answer your question, if your reference B24 is 6 columns to the left of where your formula is, then no matter where you fill the formula the reference will change to a reference 6 columns to the left, except in cases in which thats not possible (like if you suddenly paste that formula into cell C2, well there arent 6 columns to the left of column, this wont work then and you will get a REF error.)

[mainemojo]

xlnitwit ... Yes. I have a bunch of merged cells so I can't copy the formula with auto fill. The next cell down in the column may have a reference of B28 (because H24 is the merge of four rows H24, H25, H26, and H27). So in the next cell in the column, the formula would be:

Please Login or Register  to view this content.

Instead of using a static cell reference such as "B28", I'd rather have it say "the value of the cell 6 columns to the left of the current cell". I can do that if I use a formula such as:

Please Login or Register  to view this content.

But how would I incorporate that code into what I'm trying to do? My guess was something like:

Please Login or Register  to view this content.

But that is plagued with errors!

[JohnTopley]

Don't merge cells: it is the cause of so many problems( as you are discovering!).

And it really is helpful if we have a workbook to help with getting the correct formulae and being able to test them.

Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MrShorty]

I have suggested this before, and not got any positive feedback. The statement "the value of the cell 6 columns to the left of the current cell" sounds to me like a perfectly good relative R1C1 reference. In R1C1 notation, same row 6 columns to the left is simply RC[-6]. The INDIRECT() function can handle R1C1 references by using the optional A1 argument https://support.office.com/en-us/art...1-92b6306fa261 Something like =INDIRECT("RC[-6]",FALSE) should return the value from the cell in the same row and 6 columns to the left of the cell with the formula.

For more information about using R1C1 notation, see this help file: https://support.office.com/en-ca/art...es_in_formulas

[xlnitwit]

xlnitwit ... Yes. I have a bunch of merged cells so I can't copy the formula with auto fill. The next cell down in the column may have a reference of B28 (because H24 is the merge of four rows H24, H25, H26, and H27). So in the next cell in the column, the formula would be:

Please Login or Register  to view this content.

If you copy and paste the formula from H24 to H28, it will automatically adjust to use B28 instead of B24. I can't see what your actual issue is here- the formula already does exactly what you are asking, and achieves exactly the same as =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-6).

[mainemojo]

John - unfortunately, this was a hand-me-down spreadsheet. It has too much data to un-merge cells. Uploaded an example.

MrShorty - I understand cell references. My question is related to cell relationship. I want to take the formula, copy and paste into every cell in row H (remember, the cells are not always sequential such as H24, H25, etc, but more like H24, H28, H31, etc)

xlnitwith - I know that the offset formula gets me partially what I want. My question is how do I incorporate it into my isblank(indirect formula?

Using my attached example, I want to be able to copy the exact formula from Primary!H2 to Primary!H4 without any changes.

[Jonmo1]

I don't understand the problem.
That's exactly how the formula will work when filled down (regardless of the merged cells)

If H24, H25, H26, and H27 are merged
And then H28 H29 H30 and H31 are merged...

Put this formula in H24
=IF(ISBLANK(INDIRECT(B24 & "!B12")),"",INDIRECT(B24 & "!B12"))

And drag it down
H28 becomes
=IF(ISBLANK(INDIRECT(B28 & "!B12")),"",INDIRECT(B28 & "!B12"))


Is that not the desired result?

[JohnTopley]

Dragging down will not work if the merged cells are different sizes (Ranges) as per your posted file.

H4 is H4,H5,H6 whereas H2 is H2,H3

[mainemojo]

Jonmo1, it kind of is. The problems that I run into is what John was talking about earlier (and again while I was typing this out) - the merged cells. Earlier when I tried it, I got the error "To do this, all the merged cells need to be the same size". So if I drag it from a cell that is 4 merged rows to another that is 4 merged rows, it works. If I try dragging it to a cell that is 3 or 5 merged cells, then I get the error.

[Jonmo1]

Instead of 'Dragging' down.

Select the entire range (all cells of both merged ranges)
Re-Enter the formula as if in the first cell and press CTRL + ENTER

H2 and H3 are one merged range
H4 H5 and H6 are a second merged range

Highlight the whole range, H2:H6
Put in the formula
=IF(ISBLANK(INDIRECT(B24 & "!B12")),"",INDIRECT(B24 & "!B12"))
DO NOT PRESS ENTER
Instead, press CTRL + ENTER

[mainemojo]

Wow! That works almost perfectly! The one part it doesn't work is where the control is 7.10. The cell format is set to a two decimal number. The cell shows "7.10", but the formula bar shows "7.1" and the H cell with the formula shows #REF! (assuming because it can't find a worksheet named "7.1" as it is "7.10"). Do I need something like a ROUNDA?

[xlnitwit]

If they are all 2DP, you might use
=IF(ISBLANK(INDIRECT("'"&TEXT(B24, "0.00") & "'!B12")),"",INDIRECT("'"&TEXT(B24, "0.00") & "'!B12"))

[mainemojo]

That one works perfectly! The way I was originally thinking of fixing it was using the FIXED function or #s instead of zeros. Either way, your formula works perfectly and is much shorter than the ones I was playing around with. Thanks again!