Lookup possibly?

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,701
Office Version
365
Platform
Windows
Any way of not repeating the stage once it has been used in previous cell. I pick up these stages on another spreadsheet for calculating payments and I only have one payment per stage so only need each stage occurring once. What is the best way to achieve this?
Possibly two options.

1. For the layout of my previous post, change the C6 formula to this before copying across and down.
=IF(COUNTIF($B6:B6,LOOKUP(C$5-$B6,$C$3:$K$3,$C$1:$K$1)),"",LOOKUP(C$5-$B6,$C$3:$K$3,$C$1:$K$1))

2. Change how you pick this up on the other sheet to only pick up the first instance. If you are interested to see if that is feasible/easy give us an idea (screen shot) of what is on the other sheet and what formula(s) you are currently using there.
 

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
I am pasting the 2 sheets. 1) shows manual inputs of stages and 2) shows the payments for each stage that picks up from the first sheet

Sheet 1
Excel Workbook
ABDEFGH
7JobContract Amount5/11/201012/11/201019/11/201026/11/20103/12/2010
8Nov******
91430* * *194,415*Site**Base
101432* * *194,415*Site**Base
111433* * *209,290*Site**Base
121844* * *201,665*Site**Base
131935* * *162,172*Site**Base
141418* * *204,690*Site**Base
New jobs


Sheet 2


Any suggestions?

Thanks for all your help
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,701
Office Version
365
Platform
Windows
First thing is a hint for Excel jeanie. You can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board.

For example, for 'New jobs' above we didn't need to see any of those formulas at all and for 'New jobs by amt' it would have been sufficient to just see the row 12 formulas since all the ones below that are just repeats. In fact we probably only needed the C12 formula since I think it is just copied across and down. Use the Test Here forum to experiment with restricting the formulas.

I assume you are asking how you might get the amounts on 'New job by amt' if using my suggestion in 'New jobs'. If so, I will use the same layout for 'New jobs' as I had for my previous screen shot, apart from the fact that I have
- inserted a new column B to accommodate the Contract Amount.
- Deleted the 5-Nov column since it wasn't being used and also isn't shown on your 'New jobs by amt' sheet.

So, my 'New jobs looks like this with the formula suggested in post #11 in D6 and copied across and down (note I haven't shown all the formulas in the Excel jeanie shot - just 1)

Excel Workbook
ABCDEFGHIJKLM
1StageSiteBaseFrameLockupFixPCIPCI
2Duration01414213030301
3Cum Duration-1000014284979109139140
4
5JobAmountStart Date12-Nov19-Nov26-Nov03-Dec10-Dec17-Dec24-Dec31-Dec07-Jan14-Jan
6Job 1194,4157/11/10SiteBaseFrameLockup
7Job 2194,41511/11/10SiteBaseFrameLockup
8Job 3209,29020/11/10SiteBaseFrameLockup
9Job 4201,66526/11/10SiteBaseFrameLockup
10Job 5162,1728/11/10SiteBaseFrameLockup
New jobs




Then in 'New jobs by amt' you could use the following.

A11 and B11 copied down as far as you want.
C11 copied across and down.

Excel Workbook
ABCDEFGHIJ
4Site5%
5Base10%
6Frame15%
7Lockup35%
8Fix25%
9PCI10%
10JobValue12/11/201019/11/201026/11/20103/12/201010/12/201017/12/201024/12/201031/12/2010
11Job 1194,4159,72119,44229,16268,045
12Job 2194,4159,72119,44229,16268,045
13Job 3209,29010,46520,92931,394
14Job 4201,66510,08320,16730,250
15Job 5162,1728,10916,21724,32656,760
16
New jobs by amt
 

CTMom

New Member
Joined
Jul 4, 2004
Messages
32
Hi Peter

Thank you so much for this. This works perfectly. One question - why do you need the -1000 and 1 at either end of the Cum Duration row?

Thanks for all your help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,701
Office Version
365
Platform
Windows
why do you need the -1000 and 1 at either end of the Cum Duration row?
That relates to the LOOKUP function. Let's just extract one part of the fomula:
LOOKUP(D$5-$C6,$D$3:$L$3,$D$1:$L$1)
LOOKUP searches for the red value, in the blue range and returns the corresponding value from the green range. For LOOKUP to work, the blue range must be sorted in ascending order. If lookup cannot find the red value it drops down to the next value lower than the red value.

Look at this part of the formula in D6. The red part is
D5 - C6 = 12 Nov - 7 Nov = 5
So LOOKUP looks for 5 in the blue range. 5 does not appear in the blue range so LOOKUP drops to the next value lower than 5. This is 0 and so LOOKUP returns the corresponding value from the green range, which is "Site"

Look at this part of the formula that has been copied down to D8
LOOKUP(D$5-$C8,$D$3:$L$3,$D$1:$L$1)
D5-C8 = 12 Nov - 20 Nov = -8
So LOOKUP looks for -8 in the blue range. -8 does not appear so it drops down to the next value lower than -8. This is -1000 so LOOKUP returns the corresponding value from the green range, which is "". So, if -1000 (or some similar value) wasn't there, the formula would error because it cannot find a number lower than -8.

I didn't do the other end of the range quite as efficiently but since it is working let's leave it as is. It works basically the same. If the 140 wasn't there, then when VLOOKUP was looking for a number >139 (this will occur when the row 5 date is > 139 days ahead of the start date) it wouldn't find that number so it would drop back to the next value lower which is 139 and would return "PCI". That would mean you would get "PCI" in all cells > 139 days after the Start Date.

Hope that made some sense. As is usual, it is easier to write a formula than explain it.
 

Forum statistics

Threads
1,085,718
Messages
5,385,423
Members
401,946
Latest member
Jimmerjammer

Some videos you may like

This Week's Hot Topics

Top