New monthly excel formula help request

[thezappa]

As title, I have a spreadsheet which I would like to automate as much as possible.

What I would like to happen is:
Make the first data selection from a dropdown in cell A3, which I have created (from data on tab 2).
Then the user inputs the storey; 1,2 or 3 (on tab 2 from either column B, C or D)
When that is selected a cell further along the table in column F automatically populates it with the required number, in this case square metres (the data is also on tab 2).

If anyone can help that would would be fantastic, I think I need a VLOOKUP and IF formula but cannot work out how to combine them or indeed I even need those two.

https://www.dropbox.com/s/a69vp9gteeh0sl0/Floor plan test.xlsx?dl=0

 

[Grrrrr]

In cell F3 you need this formula:

=VLOOKUP($A3,Sheet2!$A$1:$E$18,[COLOR="Yellow"]<columnnumberhere>[/COLOR],FALSE)

What this does is look for the value in A3 (which is 1B1P) in the first column of the range Sheet2!$A$1:$E$18. It goes down that first column and stops where it finds the first match, in this case - on row 2 . It then looks in the column specified by the yellow text. If it's a 1 storey dwelling you need the number 2 because the data is in the second column in the range.

So the simplest solution for the yellow text would be

=VLOOKUP($A3,Sheet2!$A$1:$E$18,[COLOR="Yellow"]B2+1[/COLOR],FALSE)

A more robust solution would be:

=IF($B3="","",VLOOKUP($A3,Sheet2!$A$1:$E$18,[COLOR="Yellow"]MATCH($B3&" Storey dwelling",Sheet2!$A$1:$E$1,0)[/COLOR],FALSE))

Where MATCH looks for the specific string "<number> storey dwelling" in the first row of the target range and returns the matching column number.

 

[thezappa]

Thats great thank you very much.
Strangely I copied and pasted the first formula and it didn't work, I get #value!.

=VLOOKUP($A3,Sheet2!$A$1:$E$18,B2+1,FALSE)

Still I used the other one and it's worked, I've put that in and its working nicely, thanks again.
Only thing is the info on tab 2 in cell B2 has to have two numbers in 39 and (37) due to that property type potentially having a shower and therefore smaller floor area.
Have changed that cell to have 39 (37) and now the IF formula in H3 is giving a fail result even though the number in G3 is obviously higher:

https://www.dropbox.com/s/1z38ep4v6w3nd12/Screen Shot 2016-07-28 at 19.22.36.png?dl=0

Can't get the picture attachment to work

 

[Grrrrr]

It's because

39 (37)

is text, not a number. It's (in this case) meaningless to ask whether a number is bigger than text

 

[thezappa]

So, since using this I've now somehow become the go-to guy for excel queries, not ideal.

Anyway, I would like to automate it more and have tried to replicate a similar action to the above for the second to last column 'National space standards storage...' so when the value in column A is entered the required space is populated in column AB.
I also thought the 'Bed' and 'persons' columns could be automatically populated when column A data is selected. Not sure about this one is possible though, would it need a lot of 'IF' formula? Am happy to be proved wrong though.

I had also thought it would be good to have the pass or fail to include an 'N/A' if the adjacent (to the left) column has N/A input.

Anyone any ideas? Any help would be very much appreciated.
Thanks

 

[Dazzy]

Anyway, I would like to automate it more and have tried to replicate a similar action to the above for the second to last column 'National space standards storage...' so when the value in column A is entered the required space is populated in column AB.

Are you just trying to create a formula to read the information in Column E of Sheet2 based upon the value in Column A of the 'Space standards' sheet? If so a slightly amended vlookup formula similar to the one previously provided could be used.

=IF($A3="","",VLOOKUP($A3,Sheet2!$A:$E,5,FALSE))

Bearing in mind the above would not differentiate between House/Flat the way your current data table is set up.

I also thought the 'Bed' and 'persons' columns could be automatically populated when column A data is selected. Not sure about this one is possible though, would it need a lot of 'IF' formula? Am happy to be proved wrong though.

Are the numbers before B and P in the plot name the relevant bed/persons number? If so (assuming the number of beds/people is also a single digit and in the same format) a simple solution could be to use Left/Mid formulas to pull out the required number from the plot name cell.

=LEFT(A3,1)
=MID(A3,3,1)

I had also thought it would be good to have the pass or fail to include an 'N/A' if the adjacent (to the left) column has N/A input.

Assuming I am understanding you correctly you could wrap an ISNUMBER formula around your existing IF function to do this.

=IF(ISNUMBER(G3),IF(G3>F3,"Pass","Fail"),"N/a")

So, since using this I've now somehow become the go-to guy for excel queries, not ideal.

Given your comment above and both as a serious point and a general disclaimer I would be very careful about making sure you fully understand what any formulas you use are doing, and where they could go wrong or not work how you intended.

Some of the formulas you are asking for help with (vlookups etc) are quite easy to learn, so it would probably be worth you spending some time to learn the basics before applying them for professional use. There are lots of helpful tutorials online.

I would not use any of the above formulas without being able to verify that the work in the way you want.

 

[thezappa]

Are you just trying to create a formula to read the information in Column E of Sheet2 based upon the value in Column A of the 'Space standards' sheet? If so a slightly amended vlookup formula similar to the one previously provided could be used.

=IF($A3="","",VLOOKUP($A3,Sheet2!$A:$E,5,FALSE))

Bearing in mind the above would not differentiate between House/Flat the way your current data table is set up.

Yes. So the person using the sheet would select the value in column A from a drop down and they complete column B. That in-turn populates column F.
It doesn't matter that it won't differentiate between houses or flats. The only concern is the space requirements for that many bedrooms and that many people.
I had the idea that having selected the column A value it would fill in the storage required (on sheet 2, column E)

Are the numbers before B and P in the plot name the relevant bed/persons number?

Yes

If so (assuming the number of beds/people is also a single digit and in the same format) a simple solution could be to use Left/Mid formulas to pull out the required number from the plot name cell.

=LEFT(A3,1)
=MID(A3,3,1)

Assuming I am understanding you correctly you could wrap an ISNUMBER formula around your existing IF function to do this.

=IF(ISNUMBER(G3),IF(G3>F3,"Pass","Fail"),"N/a")

See what you're saying there but the formula in column H only has to be 'pass' or 'fail'. So if the 'G' value is higher than 'F' it passes.
The N/A part I was asking about is really for the additional bedrooms columns. So if a property doesn't have two bedrooms the measurement is 'N/A'.

Not sure I'm with you on the LEFT and MID formula you've mentioned. By having these in columns D & E would those populate when columnn A is filled in?

Given your comment above and both as a serious point and a general disclaimer I would be very careful about making sure you fully understand what any formulas you use are doing, and where they could go wrong or not work how you intended.

Some of the formulas you are asking for help with (vlookups etc) are quite easy to learn, so it would probably be worth you spending some time to learn the basics before applying them for professional use. There are lots of helpful tutorials online.

I would not use any of the above formulas without being able to verify that the work in the way you want.

I know what you mean. The excel guru 'reputation' I think is more in jest (I hope anyway). I've made it quite clear I didn't write the formula and am not keen to use complicated formula partly because of the repercussions but also because I won't be able to fix it.

In any case I really appreciate your help

 

[Dazzy]

Not sure I'm with you on the LEFT and MID formula you've mentioned. By having these in columns D & E would those populate when columnn A is filled in?

The left formula would read the 1st character of whatever is in column A, and the mid formula the 3rd character from the left. This would change depending on what is in the referenced cell. Probably worth you putting the formula into the spreadsheet and having a play to understand it fully.

The N/A part I was asking about is really for the additional bedrooms columns. So if a property doesn't have two bedrooms the measurement is 'N/A'.

It sounds like addding another layer of an IF function could be helpful to you here. Have a google on 'nested IF functions'.

An extra condition such as if there are more than 2 bedrooms etc, could be wrapped around your existing formula. E.g. In cell something along the lines of:

= IF (D3>2, (whatever your current formula is),"N/a")

If you spend a couple of hours getting yourself familiar with IF functions / vlookups in general then you should be able to do most of what you are trying to accomplish without much issue.