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

 

[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

 

[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

 

[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