## Help I am going crazy with this formula.

MS EXCEL

### Next

• 1. How do I use the SumIF function with a criteria of a cell color?
Hi, I am trying to use the SumIF function and use a criteria based on the color of the cells. I see I can sort and filter based on cell color, but cannot find how to add based on the cell color. Is this available, or do I need to wait for Microsoft to create a =color() function?
• 2. XL2002 - VLOOKUP with variable Sheet Name
Hi All I am trying to create a formula to look up data on a sheet that has a variable sheet name. Here's the current formula: =VLOOKUP(E7,'ac1'!A1:D10,3,FALSE) What I need to do is change the 'ac1' to another name based on which sheet the user is looking at. The sheet name could be ac1, ac2, ac3 etc - the number that appears in the sheet name exists in cell A1 on the sheet that the formula is in. I have tried creating the sheet name in cell B1 using ="ac"&A1, and then trying to reference that in the vlookup formula but it doesn't work. Any help gratefully received. Regards Trevor Williams
• 3. Autofilter with pivot table
I would like to create an autofilter on a table, but without the usual autofilter mode. I would like it to be as a pivot table, with the "pages fields" on top & the rest of the other fields as in the exell file. I cant find a way to organise the data to do that. Could you please help me, I'm having a headacke.
• 4. Write a formula using two arrays, across two worksheets
What I want to do is write a formula that will gather information from one column in a different worksheet (same workbook) based on a condition, then using the results to further look another column and get results based on another condition. For example, I want to know how many times EU3 appears in column E, and then of those appearances then find out how many of those are <4 in column N. (How many times does = less than 4? answer is 3 Column: E F N O P EU3 0 0 F Y EE3 15 14 H Y 579 6 4 H Y EU3 4 3 K Y EE3 4 3 H Y Thanks for any help! Susan
• 5. Multiple If Statements in the same cell
I am building a spreadsheet to display all documents I have sent someone. In cell A1, I have created a drop-down list of households, and now in column B, I want B1 to display a list of accounts that corresponds to the household in A1. How do I crate multiple if statements for B1? For example, If I select the Smith family in A1, I want to see a drop-down list in B1 of all smith accounts, but then if I select the Jones family in A1, I want to only see the Jones Accounts in B1. Thanks!

### Help I am going crazy with this formula.

```whatever number I enter into cell M2 I would like for that number to
show down the whole column K.

Ex. in cell M2 I enter the number 100, I should then see 100 in column
K in the range of k2:k51.

How can I make this possible. Any help would surely be appreciated.

Thank you in advance. I know this may sound silly but its hurting my
brain. lol

Lazarus :eek:
in Ca.

--
laz

------------------------------------------------------------------------
laz's Profile:  http://www.**--****.com/

```

### RE: Help I am going crazy with this formula.

```Type in K2: =\$M\$2 and fill down until K51!
Regards,
Stefi

azezt rta:

>>
>> whatever number I enter into cell M2 I would like for that number to
>> show down the whole column K.
>>
>> Ex. in cell M2 I enter the number 100, I should then see 100 in column
>> K in the range of k2:k51.
>>
>> How can I make this possible. Any help would surely be appreciated.
>>
>> Thank you in advance. I know this may sound silly but its hurting my
>> brain. lol
>>
>> Lazarus :eek:
>> in Ca.
>>
>>
>> --
>> laz
>>
>>
>> ------------------------------------------------------------------------
>> laz's Profile:  http://www.**--****.com/
>>
>>
```

### Re: Help I am going crazy with this formula.

```Select K2:K51
Type 100
Hit Ctrl-Enter rather than just Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

http://www.**--****.com/
http://www.**--****.com/

```

### Re: Help I am going crazy with this formula.

```Hi,
laz

if u want to enter value M2=100 , which would appeared in cell K2:K51
u have to enter formula in cell K2 :K51 = \$M\$2

the purpose of using " \$" before M is to Fix Index of Row whereas
putting "\$" after M indicates that Column Index has been fixed.

hence by Putting "\$M\$2',  Row as well as Column index has ben fixed
which gives value of cell M2 in copied cell.

;)  ;)  ;

```

### Re: Help I am going crazy with this formula.

```thank you everyone who took the time to help me, may you be blessed 1
times over.

la

-----------------------------------------------------------------------
laz's Profile:  http://www.**--****.com/

```

```I do all the things that help said to and it will not go in, nothing shows up
on the labels I have a list set up in excel and I can not get it into word as
mailing labels it is driving me crazy. I tried it as a worksheet and as a
list.
I open excel and enter it, as follows
Frodo frank 2541 west St. Hobbit pa 19130
I saved it as both a work sheet and tried it as a list, as well at a
different time. and tried both ways.
I have 600 names and adresses on my full list.
I need an exact step by step way to merge from excel 2003 into word 2003
labels avery label # 5160 thats a 30 label page. I have tried to do it the
way help says to do it and the labels come up blank and it does not help.
Please do not refer me to help pages, I need a step by step directions. As in
save it as ------ data base and so on. any help will be tremendously
appreciated. I tried all their help pages no good.
Archer
```

```I do all the things that help said to and it will not go in, nothing shows up
on the labels I have a list set up in excel and I can not get it into word as
mailing labels it is driving me crazy. I tried it as a worksheet and as a
list.
I open excel and enter it, as follows
Frodo frank 2541 west St. Hobbit pa 19130
I saved it as both a work sheet and tried it as a list, as well at a
different time. and tried both ways.
I have 600 names and adresses on my full list.
I need an exact step by step way to merge from excel 2003 into word 2003
labels avery label # 5160 thats a 30 label page. I have tried to do it the
way help says to do it and the labels come up blank and it does not help.
Please do not refer me to help pages, I need a step by step directions. As in
save it as ------ data base and so on. any help will be tremendously
appreciated. I tried all their help pages no good.
Archer
```

```I am creating a tracker for my work time and I am getting fusterated at
creating this formula, any help would be apreciated:

PTO=P
Unpaid=U
Tardy Occurrence=T
Exhausted all PTO=EP
Specialized PTO=SP
Bereavement=B
Unscheduled PTO=UP
PTO Manager Override=MO
Floating Holiday=MO
Jury Duty=JD
FMLA=FM
NML=NM

These are the words I am working with, I need it to work like this:

if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO",
THEN RETURN "P" in B3 .....ECT...   This has to work all
```