Switching from Google Sheets to Excel - Referencing columns

[Luke777]

Hi all,

The company I work for has recently taken the decision to move away from Google Drive and its related products and switch over to using MS Office as our primary office solution. Big mistake in our opinion given that we mostly use basic features and do a LOT of collaborative work (where GD excels - excuse the pun - according to most). Work politics aside, I've got a fair few documents I need to shift over to Excel.

I'm struggling to find a work around for something that I assume has a very simple solution, and that is referencing an entire column from a specific start point to an indeterminate end point. For example, in Sheets I use =MATCH(x, A7:A,0) where x is whatever search_key you fancy, A7:A looks at the entirety of column A starting from A7 for your range, and 0 is the search_type.

My problem is the A7:A part, Excel 2007 refuses to play ball. Any Advice?

Thanks all!

 

[RoryA]

Welcome to the forum.

You'd use MATCH(x,A:A,0), then adjust any formulas using that result to also use entire columns.

 

[Fluff]

You need to specify an end range like
=MATCH(x, A7:A1000,0)
Just make sure that the 1000 is larger enough to cover all your data, but don't go too large.

 

[Luke777]

You need to specify an end range like
=MATCH(x, A7:A1000,0)
Just make sure that the 1000 is larger enough to cover all your data, but don't go too large.

I assume I can get around this by referencing a cell that has a formula that determines the last row in a given column. I must say, seems extremely clunky compared to the Sheets alternative, unfortunately.

 

[RoryA]

You could use dynamic named ranges, or tables, but why can't you just use the entire column? Will you have erroneous matches in the first 6 rows?

 

[Luke777]

You could use dynamic named ranges, or tables, but why can't you just use the entire column? Will you have erroneous matches in the first 6 rows?

Exactly this - might not necessarily be the first 6 but y number of rows will contain irrelevant information. The chance of there actually being an erroneous match is pretty slim, but I'd like to avoid that chance.

I'm not overly familiar with using Dynamic named ranges or tables in my particular use case - I'll look into them and see if they can work with what I'm trying to do (the MATCH formula is actually contained inside an OFFSET). So, if you see any new topics concerning named ranges in the next week or two, say hello lol

Thanks for your help though

 

[RoryA]

Just FYI, OFFSET is a volatile function, and best avoided if possible (or at least minimize the number of times it is used).