Reverse Dynamic Range

[DatMatt]

I understand dynamic range to be a name for a range that Excel would recognize ("Inbound" for example might tell Excel to reference A1:A30) Is there a way however, to get Excel to dynamically change the range by using keywords? For example, without manually selecting A1:A30, could I get Excel to look for "inbound" in one cell, then find a second occurance (which moves daily) down the column to conclude the range? I fear macros, so I can't use VBA. Also, can I tally a keyword within the range and have Excel both number and
SUM the findings within this "reverse dynamic" range in the neighbouring column?

 

[Aladin Akyurek]

What is inbound - the name assigned to a range? If so, care to post that definition?

 

[DatMatt]

Outbound is the name assigned to a range, yes. But I want to know if I can get Excel to automatically change the range by using keywords. Starting and stopping the range with the same keyword would be best. Then I could use the word "inbound" as a range parameter as opposed to a dynamic range.

 

[Aladin Akyurek]

Instead of offering an abstract solution which fails (for me anyways) to describe the problem you have, please try clarify it by way of an example and give also the definition of outbound.

 

[DatMatt]

Sorry it took so long to respond, what with the holiday and all (and I most certainly do appreciate your time today). I'm not very good with Excel, so let me paint you the picture (I don't know how to attach an ACTUAL picture). In column one, A1 has "outbound" typed in it. A2 has TX. A3 has "Anthony" with NM in A4. Each even numbered cell has a state abbreviation, while the odd numbered cells have city names. At the end of the range will be the word "backhaul". This represents the end of the range. Directly under "backhaul" would be an empty cell, then in the next empty, odd numbered cell would be a new "outbound" marking the beginning of a new range to count down till the ending "backhaul". The number of cells with city and state names will vary daily down the column. The names will always be the same (say 20 cities and four states). Is it possible for Excel to count the number of cities between each "outbound" and "backhaul"? I could even use a symbol for Excel to look for in the city names, like putting an "_" at the end of every city name so that's all it has to look for (state names are of no matter). Also, across form every city name form "outbound" to "backhaul", I would need Excel to count off each city name.

 

[Aladin Akyurek]

Hard to follow.

You seem to have values from A2 on rightwards, consisting of cities and states, say A2:Z2, in every second cell.

If you want to count cities:

=SUMPRODUCT(--(MOD(COLUMN(A2:Z2)-COLUMN(A2),2)=0),1-(A2:Z2=""))

If you want to count states:

=SUMPRODUCT(--(MOD(COLUMN(A2:Z2)-COLUMN(A2)+1,2)=0),1-(A2:Z2=""))

Although not sure whether the foregoing is what you require.

 

[DatMatt]

Thanks for responding. The only difference is, I don't know what the range will be. I cannot know that it will be A2:A7 or A2:A22. I have to start each new "outbound" and "backhaul" session below the last, so only one column. The first session may be A2:A9, but the next session would begin below this one. My main question is weather you can get Excel to look for keywords in a column and thereby automatically use these as parameters for creating a range. Appologies if I'm none too clear.

 

[Aladin Akyurek]

A sample would help...

 

[DatMatt]

Sorry, IDK how. I see no way to attach a file or upload a screenshot.

 

[hiker95]

DatMatt,

I see no way to attach a file or upload a screenshot.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.