If then question for text?

[Financeer]

Greetings all,

I'm hoping to create a dashboard filtering information from another sheet in the workbook. The main data sheet has different statuses available followed by additional data points i.e. "Available" "Unit Number" "price" "contact information" "Location" etc"

I was hoping to create a formula on the dashboard page that would effectively say if available then show "unit number" "price" "location"

The data can be displayed in different cells in the same row just as it is on the original datapage. So essentially I would be creating the formula for one term and dragging the formula across the row, changing the data points in the quotes (I think).

It appears to be a bit more complex than I originally planned. Should I use a different formula all-together?

Any insight is greatly appreciated.

 

[rconverse]

How is your dashboard setup? How is your data set up? What are these statuses of, products? Do you choose a status from a drop down or enter it into a cell and the you want to pull all products in that status?

 

[Financeer]

Thanks and sorry for the lack of clarity. This is for an investment real estate property with 80 odd units. For the statuses on the datapage I will likely have 3-4 terms for example:

Closed
Not Available
Available
Under Contract

If a unit is Under Contract, I would like to show sales price, contact information, action items with deadlines.

I would likely manually enter the four statuses, I'll save the dropdowns for a second lesson as I have minimal experience with them. I will be the only person altering the datapage so I am comfortable with this for now.

I've done it before by cheating and creating tabs for each of the statuses and subsequent summary tabs for each category, but it gets redundant and confusing.

 

[=ODIN=]

=IF(Sheet1!$A1="available",Sheet1!B1,"")

assuming your data sheet is named sheet1 and that you status start in cell a2 of sheet1 and that all the data points next to each other and start incell b2, this formula could be used on another sheet in the work book and be dragged across and down .

 

[rconverse]

Okay, to be clear, you would like to enter "closed" in a cell ($A$1 for example) and have that pull up all of your records for "closed" units on another sheet that would hold "closed" records as well as those other status' records. Right?

 

[Financeer]

Yes, you are correct. Odins method works too (tyvm!) however it leaves blank rows until it finds the next unit with the same category.

EX:if unit 101 is closed and 102 is available and 103 is closed, my formula on the dashboard page leaves a blank row where unit 102 would be then fills in with unit 103. I think your method would solve this issue.

Currently I have this through Odins formula

Closed Units
101 Sales Price, datapoint 2, datapoint 3 etc.

103 Sales Price, datapoint 2, datapoint 3 etc.

I would like this:
Closed Units
101 Sales Price, datapoint 2, datapoint 3 etc.
103 Sales Price, datapoint 2, datapoint 3 etc.

Available Units
102 Contact #555-1234, datapoint 2, datapoint 3 etc.

Thanks for continued help!

 

[rconverse]

Yeah, I knew that would happen with that reply (missing paces). Can you set up a filter and set it only not to pick up blanks? That may work. Otherwise, you may have to get a little more creative.

 

[=ODIN=]

Yeah, I would just setup the autofilter. Otherwise, the only way I would know to do it would be through a macro which is probably more work than it's worth considering there's the filter option.

Just highlight the top row of your dashboard and hit (alt, D , F, F) that will enable autofilter. Then just filter for non blanks on your status column.

 

[Financeer]

Will do all. Thank you sincerely.