Excel Formula Help For Dynamic Cell Range:

[MattDW]

I am in need of some help constructing an Excel formula that is capable of dynamically detecting the last cell with data in a column, in place of inputting a straight range such as C3:C38. The version of Excel I am using is 2010.

I have some screen captures of this spreadsheet that I've included here to illustrate what this spreadsheet looks like as well as specifics that might help in answering this question: http://staffweb.psdschools.org/mway/excelquestion/

Essentially this spreadsheet was something I constructed to help aide myself and colleagues in my building to construct a collection query in SCCM by inputting computer numbers in columns B. In Column C I have a CONCATENATE formula that adds a prefix to the adjacent value from the cell in column B.

The cells with the blue colored text are those where a colleague or I would enter text. The other, black (non-bolded header text cells), consists of formulas.

The spreadsheet uses a VBA macro to construct a custom concatenate function that takes a range of cells (C3:C35) and combines these into the SCCM collection query that we can then copy and paste into the SCCM program.

The custom concatenate formula used in cell C40 is: ="select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = """&ConCatDelim(C3:C35,""" or SMS_R_System.NetbiosName = """)&""""

A piece that will vary from use to use, is the end-range of cells containing data in column C, as this will be dependent on how many computers need to be entered for a specific collection. This could be as few as 4 cells in column C entered or as many as 36.

As I will be sharing this with colleagues in my organization to also use I was looking to try and change the static range in the above formula (C3:C35) so that the end portion of that range dynamically corresponds, instead, to the last cell in column C with data, instead of being hard coded. This way my colleagues won't have to try and modify the range in this formula, less they inadvertently change something in the formula that results in an error.

If I simply make my end range a static C38 then I end up with added prefixes added to the formula in C40, due to the CONCATENATE formula that has been filled down to the bottom of the range (row 38) in column C.

Is there a way to change the cell range C3:C35 , in the above formula, out with another sub-formula so that it automatically detects the address of the last cell in the range? I've been researching and experimenting with this for a day or so but so far I haven't perfected a formula that gives me a proper result.

The other wrinkle in this is that column C does not contain any blank cells due to the CONCATENATE formulas that has been filled down in that column. However column B (which contains blank cells until a value has been filled in by a user) does contain blank cells and could be used to determine last cell containing data. Is there a way to use column B to evaluate the last used cell and then display the value of the adjacent cell (cell with the same row number) in column C? Would this be a MATCH or a VLookup formula?

Thanks much for your help with this!

Matt

 

[Andrew Poulsom]

Does this work for you?

=ConCatDelim(C3:INDEX(C:C,MATCH(9.99999999999999E+307,B:B)))

 

[MattDW]

Does this work for you?

=ConCatDelim(C3:INDEX(C:C,MATCH(9.99999999999999E+307,B:B)))

Thanks for your response Andrew! It's curious. When I use the formula: ="select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = """&ConCatDelim(C3:C35,""" or SMS_R_System.NetbiosName = """)&"""" I receive the output:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = "PRE12345" or SMS_R_System.NetbiosName = "PRE12346" or SMS_R_System.NetbiosName = "PRE12347" or SMS_R_System.NetbiosName = "PRE12348" or SMS_R_System.NetbiosName = "PRE12349" or SMS_R_System.NetbiosName = "PRE12350" or SMS_R_System.NetbiosName = "PRE12351" or SMS_R_System.NetbiosName = "PRE12352" or SMS_R_System.NetbiosName = "PRE12353" or SMS_R_System.NetbiosName = "PRE12354" or SMS_R_System.NetbiosName = "PRE12355" or SMS_R_System.NetbiosName = "PRE12356" or SMS_R_System.NetbiosName = "PRE12357" or SMS_R_System.NetbiosName = "PRE12358" or SMS_R_System.NetbiosName = "PRE12359" or SMS_R_System.NetbiosName = "PRE12360" or SMS_R_System.NetbiosName = "PRE12361" or SMS_R_System.NetbiosName = "PRE12362" or SMS_R_System.NetbiosName = "PRE12363" or SMS_R_System.NetbiosName = "PRE12364" or SMS_R_System.NetbiosName = "PRE12365" or SMS_R_System.NetbiosName = "PRE12366" or SMS_R_System.NetbiosName = "PRE12367" or SMS_R_System.NetbiosName = "PRE12368" or SMS_R_System.NetbiosName = "PRE12369" or SMS_R_System.NetbiosName = "PRE12370" or SMS_R_System.NetbiosName = "PRE12371" or SMS_R_System.NetbiosName = "PRE12372" or SMS_R_System.NetbiosName = "PRE12373" or SMS_R_System.NetbiosName = "PRE12374" or SMS_R_System.NetbiosName = "PRE12375" or SMS_R_System.NetbiosName = "PRE12376" or SMS_R_System.NetbiosName = "PRE12377"

The above is a valid SCCM query in the correct format.

When I changed the formula to ="select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = """&ConCatDelim(C3:INDEX(C:C,MATCH(9.99999999999999E+307,B:B))) &"""" I received the output:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = "PRE12345PRE12346PRE12347PRE12348PRE12349PRE12350PRE12351PRE12352PRE12353PRE12354PRE12355PRE12356PRE12357PRE12358PRE12359PRE12360PRE12361PRE12362PRE12363PRE12364PRE12365PRE12366PRE12367PRE12368PRE12369PRE12370PRE12371PRE12372PRE12373PRE12374PRE12375PRE12376PRE12377"

Then I saw that I needed to add back in the piece of plain text code that outputs text every time the formula wraps (,""" or SMS_R_System.NetbiosName = """)&"""") so I modified your formula to: ="select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = """&ConCatDelim(C3:INDEX(C:C,MATCH(9.99999999999999E+307,B:B,""" or SMS_R_System.NetbiosName = """)))&"""" but then I get a #VALUE! error.

Any idea where I am going wrong in my formula?

Thanks much Andrew!

 

[Andrew Poulsom]

Shouldn't it be?

ConCatDelim(C3:INDEX(C:C,MATCH(9.99999999999999E+307,B:B)),""" or SMS_R_System.NetbiosName = """)

 

[MattDW]

Shouldn't it be?

ConCatDelim(C3:INDEX(C:C,MATCH(9.99999999999999E+307,B:B)),""" or SMS_R_System.NetbiosName = """)

Yep, abolutely correct, and it works perfectly! Thanks much Andrew!

Matt

 

[MattDW]

For prosperity, and anyone else who may come across this post, the final combined formula was:

="select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = """&ConCatDelim(E3:INDEX(E:E,MATCH(9.99999999999999E+307,D:D)),""" or SMS_R_System.NetbiosName = """)&""""

Which outputs:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.NetbiosName = "PRE12345" or SMS_R_System.NetbiosName = "PRE34564" or SMS_R_System.NetbiosName = "PRE12346" or SMS_R_System.NetbiosName = "PRE65324" or SMS_R_System.NetbiosName = "PRE23465" or SMS_R_System.NetbiosName = "PRE12347" or SMS_R_System.NetbiosName = "PRE65325" or SMS_R_System.NetbiosName = "PRE34565" or SMS_R_System.NetbiosName = "PRE34566" or SMS_R_System.NetbiosName = "PRE12348" or SMS_R_System.NetbiosName = "PRE65326" or SMS_R_System.NetbiosName = "PRE23543" or SMS_R_System.NetbiosName = "PRE76236" or SMS_R_System.NetbiosName = "PRE94323" or SMS_R_System.NetbiosName = "PRE12344" or SMS_R_System.NetbiosName = "PRE65322" or SMS_R_System.NetbiosName = "PRE65318" or SMS_R_System.NetbiosName = "PRE65321" or SMS_R_System.NetbiosName = "PRE34563" or SMS_R_System.NetbiosName = "PRE16354" or SMS_R_System.NetbiosName = "PRE12349" or SMS_R_System.NetbiosName = "PRE34567" or SMS_R_System.NetbiosName = "PRE65319" or SMS_R_System.NetbiosName = "PRE23466" or SMS_R_System.NetbiosName = "PRE23468"

Thanks for your help with the dynamic range index/match formula, Andrew!

Matt