0

I have a problem which I've actually managed to solve but I'm currently using around 60 IF-statements which seems ridiculously inefficient. Two workbooks have the same table in them (see pictures below) but one is filled with standard values and the other has houses which need to have the parts required entered. I need to check the value in the "House Type" field on the workbook that needs to be filled in and copy across the values in the same columns in the "Std House Types" workbook with the correct house type heading.

Portion of the IF statement: =IF(H$7='Std House Types'!$H$7,'Std House Types'!$H14,IF(H$7='Std House Types'!$I$7,'Std House Types'!$I14,IF(H$7='Std House Types'!$J$7,'Std House Types'!$J14,IF(H$7='Std House Types'!$K$7,'Std House Types'!$K14,IF...

Where the 7th row is the row with the House Types in it on both sheets.

Essentially I am trying to find what is in a cell H7, I7, J7... in the same row on a sheet called "Std House Types" and then take the corresponding values in the table in the same column and copy them to the column in the table in the sheet we're working from.

The formula also has to be able to be filled down and across any number of rows to fill in the whole table.

Table values come from

enter image description here

Table values should go into

enter image description here

Improve this question
4
  • 1
    Could you upload a better screenshot where we can see header and column names? Like column A,B,C and row 1,2,3
    – nixda
    Jul 4, 2013 at 13:11
  • That's the extra screenshot added, would you like one of the other worksheet as well?
    – Paul Hay
    Jul 4, 2013 at 14:09
  • 1
    If its helpful, yes. You could upload it to imgur and insert a textlink only (not an image at all). This way, your question doesn't get too big. Btw. can't the old image be removed? isn't it redundant now that you have added that second image?
    – nixda
    Jul 4, 2013 at 14:12
  • Is that the kind of thing you were looking for?
    – Paul Hay
    Jul 4, 2013 at 14:17

1 Answer 1

Reset to default
0

Received some help from someone else in my office, ended up using a HLOOKUP formula to input the data into the table.

The formula is: =HLOOKUP(H$7,'Std House Types'!$G$7:$BY$364, ROW(Valuation!H9)-1,FALSE)

H$7 - House Type (e.g. “Flat 1”) 'Std House Types'!$G$7:$BY$364 - The table we are collecting values from. ROW(H9) – 1 = Get the current Row number and subtract the number of rows above the start of the table in this case H9

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .