Help needed - vlookups and conditional formatting

[Excelnewbie11]

Hi All</SPAN></SPAN>

Getting really confused and could do with some help please.</SPAN></SPAN>

Firstly, what I’m trying to achieve. I want to have a spreadsheet that can be used to track performance. Essential a system run report will be copy and pasted into a “Data” sheet and the relevant fields will be displayed in another sheet called “ALL”. The information displayed will show (through cell colour) people performance per week on a daily basis </SPAN></SPAN>

Workbook name (Data) – this is an example there are about 15 headings (comes from a system run report)</SPAN></SPAN>

[TABLE="width: 398"]
<TBODY>[TR]
[TD]Name</SPAN></SPAN>
[/TD]
[TD]Calls</SPAN></SPAN>
[/TD]
[TD]Messages</SPAN></SPAN>
[/TD]
[TD]Deals</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Activity Analysis </SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person One</SPAN></SPAN>
[/TD]
[TD]7</SPAN></SPAN>
[/TD]
[TD]9</SPAN></SPAN>
[/TD]
[TD]0</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Person Two</SPAN></SPAN>
[/TD]
[TD]13</SPAN></SPAN>
[/TD]
[TD]8</SPAN></SPAN>
[/TD]
[TD]0</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Person Three</SPAN></SPAN>
[/TD]
[TD]2</SPAN></SPAN>
[/TD]
[TD]0</SPAN></SPAN>
[/TD]
[TD]0</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Ect</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals</SPAN></SPAN>
[/TD]
[TD]44</SPAN></SPAN>
[/TD]
[TD]35</SPAN></SPAN>
[/TD]
[TD]0</SPAN></SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


I have a workbook (Named ALL) that displays data for 5 days like below – This is replicated 5 Times on the actual sheet</SPAN></SPAN>

[TABLE="class: grid, width: 537"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name</SPAN></SPAN>
[/TD]
[TD]Sales Calls</SPAN></SPAN>
[/TD]
[TD]Spec CV's</SPAN></SPAN>
[/TD]
[TD]Meetings</SPAN></SPAN>
[/TD]
[TD]Req</SPAN></SPAN>
[/TD]
[TD]Candidate Calls </SPAN></SPAN>
[/TD]
[TD]CV's Sent</SPAN></SPAN>
[/TD]
[TD]Interviews</SPAN></SPAN>
[/TD]
[TD]Deals</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD]0</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD]#REF!</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I also have another worksheet which will be used to store all the raw data (as above). The fields displayed per day in the ALL worksheet are a condensed format (less headings). I originally just used a vlookup to move the relevant data over from the data sheet to the ALL sheet, this worked fine until the number of people changed. i.e if I had set (for example) Totals to be in B6, C6, D6 ect then as soon as another person was added the lookups would be incorrect. They would then need to be B7, C7, D7 ect. </SPAN></SPAN>

So the question, is there a way for Excel to move the totals line based on how many people are listed? I also used vlookup to send the people names to the ALL sheet. </SPAN></SPAN>

Hopefully this makes sense. </SPAN></SPAN>



I also have another question. </SPAN></SPAN>

All the people are targeted do (per week) </SPAN></SPAN>
50 calls</SPAN></SPAN>
50 messages</SPAN></SPAN>
10 deals </SPAN></SPAN>

And Per Day </SPAN></SPAN>
10 Calls</SPAN></SPAN>
10 messages</SPAN></SPAN>
2 deals</SPAN></SPAN>

Currently I have set conditional formatting around the above to indicate green, yellow and red for performance based on the above number, my question is how can I adjust this so that I can input the number of days people are in the office. The colour of the boxes is dependent on the conditional formatting options, can this be automated based on a cell input? I.e “days in” in the data sheet. </SPAN></SPAN>

For example:</SPAN></SPAN>

Person 1 is only in for 3 days, therefore they only need to do</SPAN></SPAN>
30 Calls</SPAN></SPAN>
30 Messages</SPAN></SPAN>
6 deals</SPAN></SPAN>

Conditional formatting needs match the above or the colour will be incorrect. </SPAN></SPAN>

I’m confusing myself writing this so hopefully it makes some sense! </SPAN></SPAN>

Any help would be much appreciated </SPAN></SPAN>

Thanks
Matt

 

[VorLag]

Well, what I do with datasets that have fields that change position is I use an Indirect formula with a Match function nested within it.

So, the match formula can return either the column # that a certain value appears in within a specified row OR a row # that a certain value appears in within a specified column. You can't select multiple rows and columns because it won't work.

For example, =MATCH("Name",1:1,0) - this will look for the cell that has "Name" in it, it will look in row 1, and it must be an exact match [0]. What you'll get is the column #. If you use the Char function, you can convert it into a letter.

Char(64+Match("Name",1:1,0)) - will return the column letter

CONCATENATE(Char(64+Match("Name",1:1,0)),1) - the specific cell that "Name" is in (since we're saying it's in row 1)

=INDIRECT(CONCATENATE(Char(64+Match("Name",1:1,0)),1)) - will use the cell reference created by the concatenate function, thus this formula will return "Name"


I use this inside of a vlookup to grab the data I need when I know the column is going to change location. If it could change tabs as well or the tab name might change or something, you can include this inside of the CONCATENATE function as well.

 

[VorLag]

I almost forgot, if you are using this to pull in a whole column of data, then you will need a helper column.


So, take the following for example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]ID[/TD]
[TD]Day[/TD]
[TD]Color[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]001[/TD]
[TD]Monday[/TD]
[TD]Green[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]002[/TD]
[TD]Tuesday[/TD]
[TD]Purple[/TD]
[TD]$1.50[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]003[/TD]
[TD]Friday[/TD]
[TD]Blue[/TD]
[TD]$0.75[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]004[/TD]
[TD]Monday[/TD]
[TD]Red[/TD]
[TD]$3.00[/TD]
[/TR]
</tbody>[/TABLE]


Let's say we need to reference ID to get information, but the stupid column is never in the same place twice and we need the ID to get the price. So, not only does it change locations, but the column reference number will also change.

Add a row in a new sheet that just has sequential numbers that will be the row numbers 2, 3, 4, 5, etc. It's that simple. Nothing special. So, Column A in New Sheet will just be the numbers and Column B will be where you have the ID number. Column C will be your VLOOKUP to pull in price for the IDs you have.

The formula will look like this:

=VLOOKUP(B2,INDIRECT(CONCATENATE("'Old Sheet!'",Char(64+Match("ID",'Old Sheet'!$1:$1,0)),":T"),(MATCH("PRICE",'Old Sheet'!$1:$1,0)-Match("ID",'Old Sheet'!$1:$1,0)),False)

That is roughly how it will look.

Hope this helps!

 

[Excelnewbie11]

Hi

Many thanks for the reply.

Ok can get that to work in prinicple, i actually need it to display the whole row (well only the cells i want to be displayed) of information based on either the name of the person or for the "total" row at the bottom. I think your method works to bring individual cells but i need it to basically move the whole row up or down

Also to be a pain, how would i point what you have written to my "data sheet"? this is "sheet 6"

=MATCH("Name",1:1,0) how do i make this look through a column rather than a row? and ccan i make this look through a range, i.e A1 through to A6?

Thanks for your help, i am a complete novice!

 

[Excelnewbie11]

Think im out my depth here!! crikey

 

[VorLag]

To change the sheet reference, just put it in front of the rows:

=MATCH("NAME",'SHEET 6'!1:1,0)

If you need to pull over the whole row, then you will likely be using a whole bunch of indirect formulas. I've had this scenario come up too (and it's a pain in the butt, I know). So, you just modify the formula slightly to take out the specified text and make that a cell reference instead. If you are using the exact same header names, then it's just a matter of changing the formula I put in the second post to look like this:

REMEMBER:
COLUMN A = Row #s
COLUMN B = Item IDs
COLUMN C = 1st Vlookup formula
ROW 1 = Headers

I'm using "Old sheet", being where the data is coming from.

=VLOOKUP(B2,INDIRECT(CONCATENATE("'Old Sheet!'",Char(64+Match(B$1,'Old Sheet'!$1:$1,0)),":T"),(MATCH(C$1,'Old Sheet'!$1:$1,0)-Match(B$1,'Old Sheet'!$1:$1,0)),False)


So, all I did here was take out the actual text and replaced it with the cell reference in my current sheet to the header row. Lock the row in your formula and you will be able to drag this formula across your worksheet. The formula will update to reference the header name in whatever column you drag the formula to.

 

[VorLag]

I have it outlined in the first post, but I know this is a lot. It made my eyeballs swim the first time I had to learn Excel gymnastics, hahaha.

So, Match will pull the # of the column that your text appears in within whatever row you specify, to convert that to a letter, you would do this:

CHAR(64+[this is where your Match formula goes])


Then, you could use that to create a cell reference with CONCATENATE:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Type[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


=CONCATENATE(CHAR(64+MATCH("NAME",'Sheet 1'!1:1,0)),1)
will give you "A1"

if you enter this:
=INDIRECT(CONCATENATE("'Sheet 1!"CHAR(64+MATCH("NAME",'Sheet 1'!1:1,0)),1))
you will get "Name" because the formula is going to look at cell At in Sheet 1

In the next post I will do something different. Keeping it separate so it is hopefully not confusing.

 

[VorLag]

So, now that you have the formula to get the location of the Name column in Sheet 1, we have Sheet 2 to pull in that information using vlookups and indirect functions.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ROW #[/TD]
[TD]ID[/TD]
[TD]Price[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]AB12[/TD]
[TD]VLOOKUP[/TD]
[TD]VLOOKUP[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]AC18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]DE22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now, we are in Sheet 2. In column A, I have created a helper column with numbers in it that my formula can reference. This makes it possible to drag the formula down and over without having to change it for every single column or row.


So, we don't know exactly where the ID column is going to be in Sheet 1.

Let me put the Vlookup function here as a reference:

VLOOKUP(Lookup Value, Table Array, Column Index, Match Type)

Lookup Value

We want to look up by ID since that is a unique identifier that we can use to pull in all of that row's data. The formula we're writing will have "B2" to start because that is the first cell with an ID in it. Lock the column in your formula so that this does not change when you drag your formula across to other columns:

$B2

Table Array

Let's say that we know the data source (that is, Sheet 1) will never go past Column T, but, since we don't know where ID is, we can't create the Array by writing 'Sheet 1'!$A:$T. Instead, we will use a formula like this:

=INDIRECT(CONCATENATE("'Sheet 1!"CHAR(64+MATCH(B$1,'Sheet 1'!1:1,0)),1),":T")

The formula is doing this:
1. Reference the cell location created inside of the INDIRECT function.
2. Create the cell location inside of the CONCATENATE function.
3. Convert whatever MATCH finds into the appropriate column letter inside of CHAR
(CHAR(65) equals A, which is why we enter CHAR(64+ [whatever]))
4. Look at the header name in the current sheet (which is in Cell B1) and find that value ("ID") in Row 1 of Sheet 1.

This is what the formula will look like before INDIRECT runs:
=INDIRECT('Sheet 1'!B:T)

So, this is our table array.

INDIRECT(CONCATENATE("'Sheet 1!"CHAR(64+MATCH(C$1,'Sheet 1'!1:1,0)),$A2),":T")

Note: the $A2 is there because that will be the row number that you're looking at on the other tab. This completes the cell location in the CONCATENATE function. We lock the column so that it is always pulling that number to create the cell location, but when you drag it down, it pulls the subsequent rows from Sheet 1.Column IndexAnd finally, we need to calculate the column index because we aren't sure how far apart ID is from Price.

So, we will use 2 match formulas. All we know is that Price is going to come after ID, we're just not sure WHERE after ID.

The formula will be:

(MATCH(C$1,'Sheet 1'!$1:$1,0)-MATCH($B$1,'Sheet 1'!$1:$1,0)+1)

This is going to find the # of the column for "Price" and subtract that number from where "ID" is on Sheet 1. We have to add 1 so that we will get the column index we need for the formula.

Notice that I locked B1 completely and only locked the row for C1. This is because we are using the ID as the lookup reference to pull in the entire row, so we don't want the formula to shift away from B1, but we DO want it to move to the next column where C1 is. We DO NOT want the row to change though because the headers are only in Row 1.


Put all of that together:


=VLOOKUP($B2,INDIRECT(CONCATENATE("'Sheet 1!"CHAR(64+MATCH(C$1,'Sheet 1'!1:1,0)),$A2),":T"),
(MATCH(C$1,'Sheet 1'!$1:$1,0)-MATCH($B$1,'Sheet 1'!$1:$1,0)+1),False)

and this formula can be dragged across your columns and down your rows with no problem and will adjust the Vlookup based on how you've set up your sheet.