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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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?
 
Upvote 0
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
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top