V-lookup and format

[kuriti]

Hey,

I am making a dashboard for work and am using lots of v-lookups t
access a spreadsheet with lots of departmental information. I have th
v-lookups for the data all figured out, my question is this: Is there a
easy way to bring the format with the data. for instance some of th
numbers are just numbers, others are percentages, thus they come to th
new cell as .23 instead of 23%. Below is an example of the v-lookup i a
using.

=VLOOKUP($B22,'[People Hub.xls]people hub'!$E$9:$R$5000,'[Peopl
Hub.xls]people hub'!F$5,FALSE)

I don't want to preset the cells format in the dashboard because i hav
set up a system where people can choose what data goes where. thus i
would work best if the format accompanied the data.

thanks for any help.

kurit

 

[dave]

In short No(easy way).
It would be helpful to know a little more. For example,
how many VLOOKUPs are there? I dont quite understand how
a vlookup is the right choice here. If people have a
choice of which data the vlookup is targeting, how does
the formula know that? Does the user change the formula?
If the user does change the formula(or a cell which is
linked in the formula), than you can apply a different
formatting based on that linked cell by using the text
function.

Tell me more.

 

[Frank Kabel]

Hi
no chance to 'copy' formats with functions!

 

[kuriti]

dave,

i made a preference sheet where they rank the different metrics tha
can be pulled. they put a 1,2,3, etc in front of the metric name o
the preference sheet:


Ranking metric name metric code concotonate
4 Worked hours/Stat 300 332000300
2 Overtime/Worked hours 301 332000301
3 Paid hours/Stat 303 332000303
1 Paid FTEs 304 332000304


then on the sheet that represents the data, say Finance, there ar
pre-formated areas that correspond to the rankings:

1 (this column is hiden)
2nd Qtr 3rd Qtr
332000117 Actual 71.7 70.
Goal 75 75

the number above (332000117) is pulled from the preference sheet. the
put their department code in a single cell that feeds all the formula
(332000) and the metric code (117) is added as a concoctonate on th
preference sheet. Therefore, the data in the table above is the resul
of a v-lookup that usses the concotonate to find the information in th
source spreadsheet.

so in short, there are alot of v-lookup formulas and they are al
identical. they get changed by the user, but only by the ranking syste
above. the reason they are ranked is because the top 2 are graphed fo
them. these people don't know much about data/graphs and this is a wa
to feed them the information.

I am not sure if all this will change the answer about formating, bu
maybe...thanks,

kurit

 

[dave]

kuriti,

Ok. I understand more than I did. Since the user changes
the rankings, I'm guessing you're allowing them to create
a report in an order or something to that affect.

IF the data associated with each ranking can be of the
same format then you can use an If function to say if the
ranking is 1, then use % format, for example.

So ... if all rank 1 items are $#.## and all rank2 items
are #.##% and the rest are #,###, you could use this
formula IF(A3=1,TEXT($C$1,"$#.##"),IF(A3=2,TEXT
($C$1,"#.##%"),TEXT($C$1,"#,###"))). [assume c1 = 8]

results
a b [column]
1 $8.5
2 850.%
3 9

You would substitute c1 for your vlookup formula.


If each ranking might have more than one formatting, then
if the format pass-thru is critical I might consider
increasing the number of ranking items so you can get to
an individual format for each rank. There are some other
options with conditional formatting(for example if number
is greater than 1 use $ otherwise use %(since percent is
usually 1-100)), but they would only apply if your results
conformed to mutually exclusive rules i.e. the range of
results between formats did not overlap.
See help under conditional formatting if you're interested
in this method.

hth,
Dave

 

[Guest]

assume c1 is 8.5 not 8

-----Original Message-----
kuriti,

Ok. I understand more than I did. Since the user changes
the rankings, I'm guessing you're allowing them to create
a report in an order or something to that affect.

IF the data associated with each ranking can be of the
same format then you can use an If function to say if the
ranking is 1, then use % format, for example.

So ... if all rank 1 items are $#.## and all rank2 items
are #.##% and the rest are #,###, you could use this
formula IF(A3=1,TEXT($C$1,"$#.##"),IF(A3=2,TEXT
($C$1,"#.##%"),TEXT($C$1,"#,###"))). [assume c1 = 8]

results
a b [column]
1 $8.5
2 850.%
3 9

You would substitute c1 for your vlookup formula.


If each ranking might have more than one formatting, then
if the format pass-thru is critical I might consider
increasing the number of ranking items so you can get to
an individual format for each rank. There are some other
options with conditional formatting(for example if number
is greater than 1 use $ otherwise use %(since percent is
usually 1-100)), but they would only apply if your results
conformed to mutually exclusive rules i.e. the range of
results between formats did not overlap.
See help under conditional formatting if you're interested
in this method.

hth,
Dave

-----Original Message-----
dave,

i made a preference sheet where they rank the different metrics that
can be pulled. they put a 1,2,3, etc in front of the metric name on
the preference sheet:


Ranking metric name metric code concotonate
4 Worked hours/Stat 300 332000300
2 Overtime/Worked hours 301 332000301
3 Paid hours/Stat 303 332000303
1 Paid FTEs 304 332000304


then on the sheet that represents the data, say Finance, there are
pre-formated areas that correspond to the rankings:

1 (this column is hiden)
2nd Qtr 3rd Qtr
332000117 Actual 71.7 70.
Goal 75 75

the number above (332000117) is pulled from the preference sheet. they
put their department code in a single cell that feeds

all
the formulas

(332000) and the metric code (117) is added as a concoctonate on the
preference sheet. Therefore, the data in the table above is the result
of a v-lookup that usses the concotonate to find the information in the
source spreadsheet.

so in short, there are alot of v-lookup formulas and

they
are all

identical. they get changed by the user, but only by the ranking system
above. the reason they are ranked is because the top 2 are graphed for
them. these people don't know much about data/graphs

and
this is a way

to feed them the information.

I am not sure if all this will change the answer about formating, but
maybe...thanks,

kuriti

.

 

[kuriti]

Dave, thanks for your help.

This follow up is for you or anybody who can answer. I think th
conditional formating approach could work, i don't want the formulas t
grow anymore...

I have used conditional formating alot in the past, but i can't seem t
figure out how to make it format the cell for % if the value is below 1
I looked in the help folder as you suggested and searched the site t
no avail.

thanks, i think i may be close to a solution.

kurit

 

[Dave Peterson]

You can't change number format with Format|conditional Formatting.