How can I make a more dynamic, self filling table?

[urbanoco.tcp]

As part of my job, I have to fill out forms. Some of that information is input into excel where I make adjustments to the data and incorporate those tables into my report.

We have to find rent comparables for our subject apartments. The rent comparables are never identical (see attached table).

I would like to be able to just paste the numbers into excel and have it figure out how many one bedrooms there are make an appropriately sized table and label each. For instance, Comparable No. 6 would have two one bedrooms labeled 6 and 6a. On some reports there might only be 4 comps for one unit type, sometimes there might be 10, but they are always under the umbrella of the 6 properties used.

The number blocks paste into excel nicely. I will have 6 lines similar to the following


I need it to take that and make something like this


into something like this


any suggestions?

 

[vletm]

@urbanoco.tcp
Oh ... 1st You should make and send a [sample] file
with clear ideas
what do You want to have .. change (before/after cases)

 

[urbanoco.tcp]

I have attached an example file with some explanations

 

[vletm]

@urbanoco.tcp
There is the [DO IT]-button, press it!
I left original (green areas) values to compare.
There is at least ONE cell ... 1c. should be 1b, or how?
Of course, You gotta copy always same place new data to 'Data'-tab.
Something like this?
Ideas ... Questions?

 

[urbanoco.tcp]

That is amazing! I wasn't expecting someone to just write it for me, but I really appreciate it.

Let me ask you this. How does this affect things off to the side. I tried playing around with it but I am not really sure what it is doing. I have cells to the sides which are pulling the info from those cells. Then I modify the values with manual inputs and some formula math happens.

I guess I will just have to try it out and wait till something breaks but hope that it doesn't.

One last question. Can this be easily extended to use 8 unit types in stead of 4?

Again thank you so much! You save me probably a months worth of tinkering. Plus, this is way more elegant than anything that I would have done.

 

[vletm]

@urbanoco.tcp
1) You have to copy new data just same place than old data Data-tab [C4:AD9] and press [DO IT]-button. No matter any formulas and so.
The result will show on Tables-tab from row 48 to below.
2) If nothing happen, try to do as I tried to tell #1 ... or ask again
3) from 4 to 8 ... yes! It those four new will be right side of those four.
It would need to change on value ... could You send a file?
4) That was the 1st version, it should clean ...
Ideas ... questions?

 

[urbanoco.tcp]

I have attached a file with pretty much all of the tables that I use for my analysis. I see that you hide the rows below each table by setting the height value to zero. What happens to those rows? Do they get cleared? Are they set to zero? It seems that they are still have the same value but are hidden.

example: i typed in some numbers along side the table and took the average. Then I got ride of a unit type and reset the table. The average didn't change, which leads me to believe that the value is still there but hidden.

Can you have it cleat the contents of the whole row? I forsee that screwing up the formulas in that row. Will the formulas get put back in when the comp is added back in?

It turns out that the other form we use which allows for 8 unit types does not match up with the first one that you made the button form. It is missing a field so it can be pasted in to the same fields.

Would it be possible to make a second button for the data for form 2? I included another table in with relevant data.

Again thank you so much. You really don't have to do the leg work on this. Just point me in the right direction.

 

[vletm]

@urbanoco.tcp
Hided row are cleared! But Your formulas makes ...
No Way!
I think that actually You don't need those 'my first values', am I right?
I think that You need something like Your made part only, am I right?
If so, it would be much easier to make straight 'You part'
with all formulas (not on sheet) which would show correct values, am I right?
And
It's possible to use same [Do It], if Datas are always same place on 'Data'-tab.
So!
Idea - You copy to Data-tables on 'Data'-sheet and press [Do It].
The Result is possible to see on 'Tables'-sheet.
What to do?

 

[urbanoco.tcp]

I think you are right about the formulas. It is easy to just paste that in from another field.

Unfortunately, I do need all the tables that are in the document at this time. The boss likes it the way the boss likes it.

As far as the 2 different forms/data tables... It is all about saving time. So if I have to past it in then figure out which values go where and copy and paste again, that doesn't really save time.

I really appreciate the time and effort you put into this. Thank you so much! Again, you have saved me about a months worth of trying to make something like this.

 

[vletm]

@urbanoco.tcp - for testing purpose
I meant something like this ...
There were some 'mystery formulas' and so ... check all, I'll modify
... somewhere were fixed values ... not anymore, sorry
Still like
1) copy Datas
2) press [DO IT]

 

[vletm]

@urbanoco.tcp
... I noticed that UNIT TYPEs >5 would be miss without this modify!