Excel return range reference

The Sandbox Software/Technology
1

I use a simple =SUMIF formula in Excel.

Let’s say that it is this: C1=SUMIF($B$1:$B$100,“GoA”,$A$1:$A$100)

Sometimes my data in A1:B100 extends beyond row 100. I’m having trouble getting dopey intern to pay attention to the size of the data that he’s pasting into cols A&B, so I’m trying to help him out by setting up a check that will raise a flag if it’s out of whack.

I can count up the number of non-empty cells in col A (or B) quite easily, =COUNT(A:A).

What I want to do is check the cell references in the formula of C1 to see what they are, in particular, the “100” part of it and then compare it to the COUNT.

Any ideas how to do that or suggestions for another approach?

(Sometimes I border my ranges with a row of repeated characters such as Jables (no, seriously, a row of "="s or "x"s or "-"s), to show that, “Hey, you’ve got to keep your data inside of these bounds,” but that’s not fool proof either.)

The real answer is “Get rid of Excel for what you’re doing here, fool,” but I don’t have that sort of power/authority.

2

sumif($B:$B,“GoA”,$A:$A)

2 Likes
3

Yeah…you’re right…that’s what I should do.

I also have a vlookup with the same issue…perhaps change that to an index fn?

4

Yeah, I always use index/match over vlookup unless it’s a very small table.

1 Like
5

Don’t add data to the end.
You need to insert it into the range.

If you are replacing data, then do the following:

  1. Paste the new data a few rows below the current data.
  2. Highlight the new data AND an empty row on top AND an empty row on the bottom, and Ctrl-C (or find “Copy” in the menu).
  3. Ctrl-V (or find Paste in the menu) this new data + empty rows INTO the current data.
  4. Delete the old data. The empty rows are to delineate the new and old data.

Done. No changing of formulae required.
This works with every formula ever. Yes, VLOOKUP, too.

6

SUMIF sucks, format-wise. Which of your columns is being summed and which has the “GoA” in it? I cannot tell at a glance.
Use SUMPRODUCT((A1:A100=“GoA”)*(B1:100)).
See, now EVERYONE knows exactly which column is being summed and which has the condition. And, add as many conditions as you want, and EVERYONE will know that they are conditions. Heck, add another cross-producting column.
Note the extra parentheses at the beginning and end to denote the SUMPRODUCT’s beginning and ending.

7

Right. This is how I would do it, but I need to protect myself from dopey intern not following instructions on how to do that.

8

I only use SUMPRODUCT when it’s going to be across files, since SUMPRODUCT will calculate when the source is closed and SUMIF will not.

9

Yes, it does. I usually use SUMIFS even if I only have one conditional statement because I like that format better.

That’s a pretty good idea. I’ll consider it.

10

Then you, as the intern’s teacher, need to emphasize how important it is, and how Excel sometimes does not know what you mean to do (like in defaulting two-digit years).

11

Does INDEX work well with entire columns?

One reason I use $A$1:$A$100 over $A:$A is to restrict how much work Excel has to do.

I think some functions work well with the $A:$A format. (Maybe they all do.) I just don’t want to bog down the calculation.

12

You can use a designated entry to denote the final one ‘XXX’ or some such. Then use a match function to set your sum range to 1 less than that. It will adjust automatically from there.

13

VALUE(LEFT(RIGHT(FORMULATEXT(C1),4),3)) is one way to check the cell reference of the formula in C1.

1 Like
14

Ooh, you’re linking files???
I am generally against that.
I have an exception, but I’m VERY careful with these conjoined files.

15

DANGER WILL ROBINSON!!!

16

I try to limit it, but I haven’t gotten the reserve IBNR allocation into R yet, so I do it on occasion.

17

Well, I did take over a person’s work once. There were files on a network drive… that linked to his laptop, which was taken and repurposed. Seems he worked mainly from his laptop (reasonable, when the network is not in your building), and when he left, he copied some files to the network, but not all of them. Yay, me, I get to start from scratch!

For links, I’ll do it only if the files are in the same folder. So, if the folder gets copied (mine are usually yearly), the links inside the files get changed automatically.

18

Ooh…new function for me to learn.

Thanks!

19

Agreed. This looks pretty sweet.

20

According to Excel Full Column References and Used Range: Good Idea or Bad Idea? | Excel and UDF Performance Stuff, who tested it on formatting, I believe, it does pretty well.