I am trying to build a spreadsheet that will help me save time with my job. I have attached the spreadsheet that I am working on.
I currently have a spreadsheet with 2 tabs - Costings, PipeOD. Sheet 'Costings' has 4 columns (A - D). Sheet 'PipeOD' has the data that I want columns C and D to automatically recognise depending on the input in columns A and B of 'Costings'.
Currently I have used an index formula to automatically recognises whatever is input in column A. This searches columns A and B of 'PipeOD' and inputs the value in column B in column C of 'Costings'.
I cannot build a formula that will enable the same kind of searching of whatever is in a cell in column b of 'Costings' and against C to G of 'PipeOD', with entry of the data in column G.
Example: In costings I want to enter Pipe OD as 1/8", this returns 10.3mm as the result of the index formula. Similarly if I entered 10.3 it would also return 10.3mm as the result. In 'Costings' I also enter 10S as the wall thickness. This should return 1.24mm in column D as per the data in 'PipeOD'. The problem I am having is that the formula first needs to search the Pipe OD size and then search columns C to G returning the value in column G for the row relevant to both the Pipe OD and the WT in columns A and B of 'Costings'.
I know this was a bit of a long-winded explanation but please help!
if you are only going to enter shd s codes in column b
=INDEX(PipeOD!$G$3:$G$178,MATCH(B6&C6,INDEX(PipeOD!$E$3:$E$178&PipeOD!$B$3:$B$178,0),0))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Please have a look at the attached.
I've created a user defined formula (Alt F11 - module1 to see the code) in order to get the weight and used a simpler IF/VLOOKUP nested formula for the OD.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
This link may help:
http://support.microsoft.com/kb/59482
In Costings!D6 enter this formula (confirm with CTRL+SHIFT+ENTER) then copy down:
=INDEX(PipeOD!$G$3:$G$176,MATCH(Costings!A6&Costings!B6,PipeOD!$A$3:$A$176&PipeOD!$E$3:$E$176,0))
Thanks for your help but I need the formula to first search the Pipe OD (both in inches and mm) and also input the WT in mm if I enter anyone of the WT codes.
E.g. Step 1. If I enter 3" or 88.9, it will display 88.9 in column C of 'Costings'.
Step 2. If I enter either STD, 40, 40S, 0.203, or 5.49 (columns C to G of 'PipeOD') it will display 5.49 in column D of 'Costings'.
Note. As the OD (outside diameter) of a pipe increases, the thickness (WT) relating to codes such as STD or 40S also increases in a non-linear fashion. This means the output in column D of 'Costings' must also be related to the OD (column C of 'costings').
I just cannot figure out how to configure this in a formula..
Cheers
try
=INDEX(PipeOD!$G$3:$G$178,LOOKUP(9000000000+307,CHOOSE({1,2,3},MATCH(B6&C6,INDEX(PipeOD!$C$3:$C$178& PipeOD!$B$3:$B$178,0),0),MATCH(B6&C6,INDEX(PipeOD!$D$3:$D$178&PipeOD!$B$3:$B$178,0),0),MATCH(B6&C6,I NDEX(PipeOD!$E$3:$E$178&PipeOD!$B$3:$B$178,0),0))))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi,
I suggest to make a simple pivot table :
- ease adaptation
- avoids data entry errors
See attached file
Best regards
Thank you very much, this works a beauty!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks