Search column and return result

viper7986

New Member
Joined
Apr 1, 2013
Messages
9
Here is my problem: I have created a workbook to manage rental property, i have multiple sheets each pertaining to a different property (housing, apt complex, strip mall, etc.) on each sheet i have a column that contains amount due for the address. What i would like to do on a new sheet called rent due report is scan the amount due column for an amount over 0$ and return the address and amount due, but have no spaces between the results.

I have tried the "=if(B5>0,A5 & "" & B5,"") but this gives me blank cells between results. I would like to do this in vba since i believe it would be easier but a formula or array would work as well.

Any help would be appreciated
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
I would use a formula, the reason they auto update, what are the sheet names? how many sheets do you have? do you have defined names?
 

viper7986

New Member
Joined
Apr 1, 2013
Messages
9
I would use a formula, the reason they auto update, what are the sheet names? how many sheets do you have? do you have defined names?

Sheet names are: Mansour Ave, The Palms, Houses, Woodlands, Deer Trace, Ball Villa, Destiny Hill, El Toro, Riverside, Riverwynd Storage. 10 sheets for the properties. i do not have defined names for the amount due columns
 

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
I think that we need some help from someone else, here is what I have so far
=INDEX(Man_Ave_Address,SMALL(IF((CHOOSE($A$3,Man_Jan_AD,Man_feb_AD,Man_Mar_AD)>0,ROW(CHOOSE($A$3,Man_Jan_AD,Man_feb_AD,Man_Mar_AD)-ROW(CHOOSE($A$3,'Mansour Ave'!F5,'Mansour Ave'!W5,'Mansour Ave'!AN5)))),ROWS($B$6:b6),0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,966
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top