Dynamic Named Range

[dssrun209]

I have two sections on a worksheet. In row 12, columns A:F I have the word "Cash" and in row 33 columns A:F i have the word "Credit Cards". In between are cash and credit card transactions respectively. In cell A1 i have the cash total and cell A2 i have credit card totals. However every month or so there may be more rows added within the cash section. Is there a formula with OFFSET or another function that will expand down to cash until the word "Credit Cards" or any other way to pick up inserted columns in the cash column so i do not have to maintain the SUM formula in A1?. The design of the worksheet cannot be change because it is more complicated than what I am describing, but I am simplifying it to solve this problem.

Thanks in advance.

[MarvinP]

Hi,

You are looking for http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.beyondtechnology.com/geeks007.shtml

hope that helps

[JBeaucaire]

If you simply put the word CASH at the beginning of each row for a cash entry and CREDIT CARDS in the same column for each row where you enter a credit card row, you not only don't have to use separate sections anymore, you can use a simple SUMIF() formula to total all the entries.

Assuming:

1) column A has the CASH/CREDIT CARDS designations starting at row 12
2) column B has the values to SUM

Put this A1:

=SUMIF(A12:A20000, "CASH", B12:B20000)

Put this in A2:

=SUMIF(A12:A20000, "CREDIT CARDS", B12:B20000)

[MarvinP]

Hi dssrun209,

I think Jerry may be talking about Structured References, discussed at
http://office.microsoft.com/en-us/ex...010155686.aspx
which came along in Excel 2007 (if I read the site correctly).
Which means, if you have 2003, it may not apply.

[royUK]

I think Jerry is actually suggesting an extra column to enter payment type. This could be entered with a Data validation list

[MarvinP]

@RoyUK
I agree, Jerry is rarely (if ever) wrong.

If dssrun would supply a sample worksheet with his problem we could show different ways to arrive at his answer. @dssrun - how about a sample? I wonder if posters ever look back to see if there is answer with questions like this?

[JBeaucaire]

I reserve the right to make bonehead assertions and erroneous conclusions based on biased methodology. Stubborness and cluelessness is my legacy.

Meanwhile, yes indeed, I am referring to a column that flags each row to a specific category, a DV drop down would be an awesome idea to insure no typos. A sample sheet would make it simplest (and shortest) to aid you.

[dssrun209]

I worked on this problem this morning and the structured references was working well and did what I want, however my fellow employees are working with 2003 Excel and I lost all those references since 2003 doesn't support this. Does this function the same way in 2003 if I create a table with a named range and name each column? I am on a ski trip this weekend so I can supply a sample worksheets Sunday night or Monday.

The dynamic ranges on ozgrid website did not work since really each section is its own table and I can't reference a whole column. And again i have to keep it this way because there are three sheets per day for the month, so departing to different sheets is out of the question. This is not my workbook, I was assigned to help reform it. So all in all, the largest sheet has three separate tables and at the top is a summary for certain aspects in the tables.

Thanks again in advance.

[dssrun209]

Here is a sample worksheet that I simplified (the real workbook are these three sheets times the number of days per month). It is setup in different tables for easier data entry so others can differentiate where to enter the data. I back this up in access and archive the workbook elsewhere and that is why I keep it somewhat in a table format for queries to pick up the data easier.

The structure references turned into ranges when I saved it as 2003 so I didn't bother with the rest of the summary formulas at the top, but hopefully you can catch what I am trying to accomplish (details on the bottom and summaries on the top, with possible macro buttons to add rows if they do not know how to (not shown in this workbook)).

I guess my question is, can this be done better, or if I created structured references in 2007 and save as 2003, will my summary formulas always update? I am not too familiar with how this will operate in 2003 besides the warnings I get when I save. Thanks for all the help!

[MarvinP]

Hi dssrun209,

Now that I've seen your workbook I have suggestions. I can see why it has been a problem to do Dynamic Ranges as your Lists/Tables move around depending on how many rows are in the data above it. I see also how you have multiple sheets and multiple tables on each sheet.

Excel was built using Rows and Columns of data called Lists in 2003 and before with the name changing to Tables in later versions. When your data is in these Tables (or Lists) the real power of Excel can be utilized.

I'd recommend that you try to get all your data into a single table instead of many smaller ones.

It looks like you use and understand validataion fields and that is great for helping with tables of data.

Looking at your example data I'd suggest Columns with these headings:
Zone, Facility, Date, Pmt/Deposit, Type, Account, Amount, To/From, Chk/Dep #, Other?, Comment:

Everytime money moves it will be entered in a single row with the above column headings. This would reduce your 3 sheets into a single large table. Then you can filter or sort your data and do real analysis with it using the power of Excel.

I'm leading to Pivot Tables which I believe would be the final answer. Before you can get to Pivot Tables you need to have your data in Tables as suggested above. Look at these sites to see where I'm going. Look at the capabilities more than the How To to see why I'm suggesting them. Also - Pivot Tables are available in 2003 as well as later versions. They just get a little easier to use and with a few more capabilities in newer versions.

You can search on Pivot Table or start here http://peltiertech.com/Excel/Pivots/pivotstart.htm

I hope this helps.

[dssrun209]

I figured that all along, I guess if they came for my advice that is what I should have said considering I work with tables most of the time (I use access a whole lot and pivot tables as well when access can't suffice). I guess I tried to make it work in their three sheets to keep to their format. What I will probably do is create some sort of data validation so they can only enter the fields they want (and everything is spelled right), with a dynamic table so the pivot table can be refreshed to pick up new data. Then this makes it easier to backup in access and I can wipe the data in excel daily so they are not viewing a large table.

Thank you everything for your input!