Fill formula containing named range but change named range based on relative reference

1

1

I have a formula that I need to copy in many cells, but I don't know how to copy a named-range reference to another cell without changing it.

NamedRange (a table imported from database connection) would look like

Col1|Col2|Col3|Col4|
Data|Data|Data|Data|
Data|Data|Data|Data|

Formula example:

=SUMIFS(NamedRange[Col1], NamedRange[Col3],$A3...)

If I copy this, for example, in different columns, the "Col1" would change to the "Col2" and "Col3" would change for "Col4".

After filling the formula over to the right, it would give:

=SUMIFS(NamedRange[Col2], NamedRange[Col4],$A4...)

How can I copy it without changing?

The exact formula, to give a better idea... Here is the formula of 1 cell:

=SUMIFS(Table_sql.project.com_wf_tbl_Rainbow_LeaveRegister[BO Code],Table_sql.project.com_wf_tbl_Rainbow_LeaveRegister[Process Status],"completed",Table_sql.project.com_wf_tbl_Rainbow_LeaveRegister[Employee No],$A10,Table_sql.project.com_wf_tbl_Rainbow_LeaveRegister[First Day of Leave],"<="&C$9,Table_sql.project.com_wf_tbl_Rainbow_LeaveRegister[Last Day of Leave],">="&C$9)

pSyToR

Posted 2013-08-22T09:30:40.940

Reputation: 131

1Are you sure? What is the exact named range name? I tried it and the named range remains the same when I copy the formula to another column? – Jerry – 2013-08-22T10:27:49.047

1Please post the exact formula you are using and post the formula that defines the named range. A range name normally does not change when copied to a different cell. Then, again, a range name can be defined with relative references, in which case, it WILL change when copied somewhere else. So, to help us troubleshoot, please provide more detail. – teylyn – 2013-08-22T10:34:33.853

It's not the "NamedRange" that changes.

In Excel Now you can use the "Column Reference" of a NamedRange to create an array inside the NamedRange so in the formula Above

NamedRange[Col1Reference] would refer to the column "Col1Reference" when I drag it, it will give "NamedRange[Col2Reference] (the 2nd column in the range) Then so on... Once at the last column if you keep dragging it will go with the first Column again. – pSyToR – 2013-08-25T03:10:35.540

Use the $ char before the column letter, such as $A1. You can do this with F4. – Doktoro Reichard – 2013-08-25T03:28:31.783

... I'm not talking about cell reference but with NamedRange Reference... – pSyToR – 2013-08-25T09:09:55.097

Moderators cannot do anything about this, so please don't use flags for getting attention to your question. My tip would be to edit your question with your recent progress. That way it's bumped to the front page. Other than that, there's not a lot that can be done if you do not have the reputation yet to place bounties, sorry. – slhck – 2013-09-24T13:27:16.543

Answers

0

Select the cell with the original formula, copy it, select the range of cells that you want to have the same formula, and paste it.

For some reason, "filling" a formula will make the column reference move, but pasting into a range of cells will preserve the original column reference.

bob

Posted 2013-08-22T09:30:40.940

Reputation: 1

0

I know this is 4 years too late, but an alternative to keeping the range references constant in the formula is to use the INDIRECT function. This thread showed up when I was searching for something similar for myself.

With your example, the SUMIFS would look like this

=SUMIFS(INDIRECT("NamedRange[Col1]"), INDIRECT("NamedRange[Col3]"),$A3...)

Hoping this answer would help others who require the same functionality.

Serendipity

Posted 2013-08-22T09:30:40.940

Reputation: 101

0

Simple way is just to copy the text in the formula bar and pasting it back there after selecting a new cell.

Reuben L.

Posted 2013-08-22T09:30:40.940

Reputation: 942

Yeah I was looking for an easy Way ;-) But thanks ehehe I have 180 cells wide spreadsheet hehe – pSyToR – 2013-08-25T03:14:22.053

Asked: 2013-08-22T09:30:40.940

Viewed: 1 546 times

Active: 2018-05-30T04:14:19.437