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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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)))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,238
Members
430,199
Latest member
Petty queen

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