Help with cross sheet formulas

steve50951

I have a property list setup that contains floor plans and costs for those floors. In another sheet i have a column setup as "bill rate."

In the bill rate column, i would like to write a formula that would go to the property list sheet, search the property name column, match the floor plan and cost of the floor plan

The cost would go in the bill rate column of the actual property sheet.

I've attached are some screen shots of the sheets i am pulling data from.

Thanks for you help!

 

Paul Newcome

You will need to provide some more info. I do not see in your screenshots where the floor plans have consistent naming between the two sheet.

steve50951

Part of the process is to go through each property and make sure floor plans are entered correctly. But here is the idea (attached in screen shots)...Once floor plans are updated correctly the idea is that the formula would calculate the bill rates.

Andrée Starå

Hi Steve,

I'd recommend using an INDEX/MATCH formula solution for this. Are the unit numbers unique? They have to be for it to find a match. (Repair, reorder?)

Would that work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

So you are trying to add up all costs for Wilson Crossing for Floor Plan 1, Floor Plan 2, Floor Plan 3, etc... individually?

steve50951

Yes, in a way. Each property we services will have multiple floor plans. Each floor plan has its own cost associated with it. when i input a given floor plan for a unit, I want the formula to go to a master list that has floor plans and costs contained in it and input the cost for that particular floor plan. So the formula needs to search a table. find the right property, and then find the corresponding floor plan. then input that cost in the column "bill rate"

steve50951

The unit #'s are unique to specific properties, but in all of our properties there may a duplicate unit # and/or floor plans. I wanted the formula to search through a table that would contain the specific property name first, then find the corresponding floor plan, and input the cost of that particular floor plan.

Paul Newcome

Ok. So you will need to use an INDEX/MATCH formula to pull all of your costs. This will require some consistency in data for the most accurate results. You can then use a SUMIFS statements to add everything together.

Andrée Starå

Did you get it working how you wanted?

Best,

Andrée

steve50951

Not yet. Been trying to put it together, but i dont have a good enough handle on Inex/search functions to get it working yet.

 

SGF

Paul Newcome

Is there any part in particular you need help with? I have found that when doing an INDEX/MATCH where multiple columns are used for verification, it is sometimes easier to use a helper column on each sheet along with a JOIN function. That way you can have a specific string of text that combines both into a unique value that can be used in your MATCH function as opposed to having nested INDEX/MATCH statements which gets to being a royal pain.

steve50951

Paul,

Thanks for your continued help.

I could use a little help to getting started.

What would be the basic formula i could use to start? Then i could just cut and paste the formula as needed?

I've attached a few screenshots to give some context.

I would like to write a formula that would calculate the billing rate for a giving property/floor plan.

-Screen shot #1-  i have the property listed in the 1st column. I have columns for the floor plans and costs associated with that particular property the floor plans

- Screen shot #2- you will see a sheet of the actual property. In this example it is wilson crossing apartments. in the actual property sheet there are unit #'s listed in the rows. Each unit # is a new row. We have a column where we input the floor plan for that unit. In that same sheet is a column for "Billing Rate."

- Screen shot #3- shows the column for billing rate. For now, I am manually inputting the billing rate. But what i would like to do is write a formula that would pull the that information from the master property list that has information listed. Floor plan. Costs, etc... (refer to screenshot #1).

That's where i need some help to just get started.

I know it's a lot, but thanks for your continued help & support.

 

Paul Newcome

It's all starting to come together now. Ok. First I am going to suggest making sure all properties and unit names are entered exactly the same across all sheets. For example, your property name in the first screenshot is "Wilson Crossing Apartments", but in the second screenshot it is just "Wilson Crossing". This can be accounted for, but opens up an additional margin of error which can grow exponentially the more inconsistencies there are. I will show examples of  the recommended as your current setup could get rather convoluted.

Here's what we will be using...

=INDEX(range you want to pull data from, row reference number, column reference number)

=MATCH(data you want to search for, range you want to search in, type of match)

.

The INDEX function displays data from a range using a row number and an option column number to determine the specific cell within the range.

The MATCH function will display a number that designates which cell within a set range the data you want to search for is found.

.

We want to pull data (INDEX function) that can be anywhere from the first Floor Plan # column to the last Cost # column and can also be anywhere from row 1 to row 5,000 (max number of rows in a sheet). We will designate this range as Range X.

.

So to use your screenshots above (assuming Wilson Crossing is on row 1 of the master sheet for simplicity)...

We are looking at your entire sheet from the first [Floor Plan #] column all the way over to the last [Cost #] column and from row 1 all the way down to the very last row.

.

We see that Wilson Crossing is on Row 1, and the Woodland floor plan is in the first column. We need the second column though to pull the Cost associated. Now that we know the row and column number (1 and 2) we can use an INDEX function to display the data.

The basic INDEX function would look like this:

=INDEX({Master Sheet Range X}, 1, 2)

.

But we can use the MATCH function to automate the row and column numbers for the INDEX function like so:

.

MATCH($[Property Info]@row, {Master Sheet Property Name}, 0) <--- Returns a 1 for the row number

MATCH($[Floor Plan]@row, {Master Sheet Range X}, 0) <--- Also returns a 1 for the column number

We then add 1 to the column number to give us the column AFTER the floor plan name which puts us in the corresponding Cost # column which brings us to our solution...

.

=INDEX({Master Sheet Range X}, MATCH($[Property Info]@row, {Master Sheet Property Name}, 0), MATCH($[Floor Plan]@row, {Master Sheet Range X}, 0) + 1)

.

{Master Sheet Range X}: Use the appropriate steps for cross sheet referencing and select ALL columns on your Master sheet from the first Floor Plan # column to the final Cost # column.

$[Property Info]@row: Leave as is. Refers to the [Property Info] column on your formula sheet for whatever row the formula is on.

{Master Sheet Property Name}: Cross sheet reference to the Master sheet selecting the column where the property name is housed.

$[Floor Plan]@row: Same as [Property Info]@row except referring to the [Property Info] column.

Andrée Starå

Ok.

I saw that Paul and you are working on a solution now.

Kind regards,

Andrée

steve50951

Paul,

Wow! Thank you so much for all of your help in explaining the index/match functions. I think i understand now how it works. Im gonna start putting it together.

This is HUGE! This is going to really help us.

Thanks again!

Hopefully I can get it working from here. ill keep you posted.

Paul Newcome

Happy to help. 

 

I did use your column names, so if you make sure the data is consistent across all sheets as far as the property and floor plan names and make sure to follow the proper steps for setting up the ranges for the cross sheet references, you should actually be able to use the formula I posted.

 

As a side note... I use INDEX/MATCH in place of VLOOKUP 100% of the time now because it is so much more flexible and less likely to break in the event of columns being rearranged and whatnot. I also find that it provides the most consistent accurate results.