sumproduct _zafar
and this one in G2:
=B2*C2 + D2*E2
Copy these down the column - an easy way is to select F2 and double-
click the fill handle (the small black square in the bottom right
corner of the cursor). Then select G2 and do the same.
Hope this helps.
Pete
On Jul 23, 9:46 am, vlook fomula
<vlookfom...@discussions.microsoft.com> wrote:
> I have two diff things in two diff column and rage of columns are 30 I always
> calculate manually by using +. Kindly advise me any easy formula and mention
> the site of temples to look the flow of application
>
> Examples
> Total
> Description Pack Kgs Pack kg Pack Kg
>
> A123 2 400 1 200 ? ?
> B123 1 200 3 600 ? ?
> C123 5 1000 2 400 ? ?
> D123 1 200 2 400 ? ?
>
> Regards
Dear Sir,
the above application could not help me actually my english is not good pry
try to understand me, actually i want to apply formulla and want to get sum
of the add and even column sepectively .
regards Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518895
sumproduct_zafar
i received a mail from the site notifyMS@microsoft.com but it is not openning
here also i could not appear my question in the surch result kindly help me Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518890
Hiii - Please help
Is there any shortcut key (like Ctl C for copy) that we can use for Coloring
a cell.
I actually need to copy certain data from an excel sheet to another system.
Since this is a fairly large data, it becomes difficult for me to remember
the last copied data. Is there any thing I could do to color the cells that
have been copied?
THis way, I will kno that all the cells colored in a particular color are
the ones that have been copied already. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518887
calculating Leave
Hi
I am trying to do annual leave calculation what i need is an
automatic
calculation for the following
The rule is
Every employee is entitled to a fully paid annual vacation after the
completion of 3 months
1 to 5 years of consecutive service fifteen (15) days per year
5 to 10 years of consecutive service seventeen (17) days per year
10 to 15 years of consecutive service nineteen (19) days per year
B
C
D
4 Name George
5 Staff Number 50004
6 Position
7 Starting Date 01-May-03
8 Total Working Days (NOW()-C7)/30/12
9 Annual Leave Days Taken Days
Remaining
10 15
+D39 B11- C10
11 Total Days Allowed
12 =5*15+(C8-5)*17
39 From Day Inclusive To Day Exclusive Total
Annual Leave
A8 =The formula that i am using to find the Annual Leave pr year is
the following
=IF(TODAY()-C7>10*365,19,IF(TODAY()-C7>5*365,17,IF(TODAY()-
C7>0.25*365,15)))
What i want is an automatic calculation for the total days allowed
instead of manually putting the formula i want it to deduct the
previous 5 years as pr the rules =5*15+(C8-5)*17
What is the formula that allows me to do that ?
what i need is an automatic formula to be updated alone on the B12
Thx in advance Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518885
EXCEL - COUNT help
I am working in excel 2003 where my data is like:
1-jul 2- jul 3-jul 4-jul total credit bal by days
0.9 0.1 0.4 0.5 1.9 1.1 0.8 xx
In place of xx above, I need a formula where it should look from 4-jul to
1-jul and give number of days from when bal is due. In above example answer
sholud be 2 as bal 0.8 falls within sum of 4-jul + 3-jul.
Need help from experts.
--
Alisaheb Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518882
Excel 2007 Autofilter vs Excel 2003
Hi everyone
I am a excel2003 user and when I just do a autofilter and copy & paste
result to a new sheet, it will only copy the data that I ve already
filtered but how come in excel 2007 it does not allow me to do that?
e.g. If I have a data from column A to G and do a autofilter from
A1toG1 and when I filter column B by "ABC" , select and copy the
filtered results. In;
Excel2003: It only copies that is "ABC" in column B.
Excel2007: It copies the whole thing.
Can anyone help?
thanks for your help in advance
Regards
james Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518864
return values in the table with multiple conditions
I have a table like this
columnA ColumnB Amount
A1= 10 0 -1.00
A2= 11 10 2.00
A3= 12 0 -5.00
A4= 13 0 -1.50
A5= 14 11 3.00
A6= 0 12 2.00
A7= 0 13 1.00
A8= 0 0 -4.00
A9= 0 14 3.00
A10= 0 0 1.00
In columnB I want to return columnA nos if the following conditions are met.
1)B1:-If amount(C1) is -ve then in B1 should be 0 otherwise it is A1.
2)B2:-if C2 is -ve then B2 is zero,and B1 is zero then return A1 otherwise
A2. I want results like in B column stoping till largest number in colA is
met.
I have tried nested 'if' with no success, Please help. Thanking you in
expectation of early reply
--
cprao
--
cprao Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518860
CREATING FORMULA IN ONE WORKSHEET BASED ON MULTIPLE CRITERIA IN AN
I am trying to take data from multiple columns in one worksheet on the same
Excel workbook, and based on the results meeting certain criteria, sum the
data in another column from the same worksheet into a table field in another
workbook.
For example, I have the following columns with multiple fields of data in
each:
Name Type Topic Hours
I want to create a formula to sum all of the hours if the Name is Joann, the
Type is Easy, and the Topic is Mystery.
Can someone help me?
Thanks in advance.
Etg Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518857
Nested Forecast Formula Help
I'm looking to write a formula that matches two criteria before calculating a
forecast. I tried using a IF AND function but the Forecast function itself
will only calculate based on one X value. I want the X value only if it
returns a TRUE matching two criteria in two different columns first. Is there
a way to calculate this?
Here is my current forecast formula:
=FORECAST(F23,'Raw Data'!$G7:$G50003,'Raw Data'!$O7:$O50003)*F13
I want to do an IF(AND function first to match two values from two different
columns in my RAW Data worksheet. The problem is that the forecast formula
only looks for one value, but I want to qualify that value based on two
criteria that match first. I hope that is clear enough. Please let me know.
Thanks Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518856
Cell Underline problem underlines across width of cell
Hi,
Need solution for cell underline problem. When I warp text in a cell and
then try to underline only the first word in that cell, it puts an underline
the width of that cell when I only wanted just the characters of the word
underline; it continues after the word to underline thereby underlining the
space after it. How can I correct this? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518847
Convert int ccyymmdd to date(time)
Hi, our legacy system keeps dates in integers in form ccyymmdd.
I've long known how to convert, in Excel, a datetime to a number. One
example is:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
But looks as I may, I haven't found a way to convert a ccyymmdd int
into a datetime.
Oh also, I'm using Office 2007. The FORMAT function, which might (?)
have helped me, seems to have disappeared.
thanx - pat Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518846
Date Calculations
I have a worksheet with a beginning and ending date range. I need the number
of months for the range but also need them rounded up to the next month.
For example:
13-Jul-11 to 31-Jul-12 should calculate as 13 months and not the 12 provided
by the DATEDIF function.
In short, I need to round up to the next month for my calculations.
05-Mar-11 to 31-Dec-11 should return 10 full months and not 9.
Is there an easy way to do this? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518844
Getting a Count
I have two columns, one of which has "2000" among other text while the other
has "No." I need to count the number of times there is "No" in a row if it
also contains "2000." From reading previous posts, if appeared that the
following would work, but it doesn't. It just brings the calculation to 0.
What is the proper of way accomplishing this? Thanks.
=SUMPRODUCT((B18:B351="*2000*")*(C18:C351="No")) Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518842
assigning grades
I want to be able to assign a grade in the excel gradebook I'm creating where
if, for example, cell B7 is 99 or greater, cell C7 shows "A+", if B7 is 92 to
98, C7 shows A, 90 to 91, C7 shows A- and on down the line for B+, B, B- etc.
I'd LOVE it if I could learn how to do it!!!
--
thanks for your response!! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518832
How do I delete the refernce a formula when copying and pasting?
When I copy from one workbook to another, the reference is copied. How do I
delete
the reference part of all my formulas on a sheet? Example:
='[4trial 2008 Wkly Summary.xlsx]WklyInfoA868'!D23
I just want: =WklyInfo868!D23 to show up in the cell. I need this
to occur
for all the formulas copied on this sheet.
Suggestions! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518827
Using SUMIF with RIGHT()
Hi,
I'm trying to SUMIF data in "data" column with the condition that last 4
characters of "condition" column match certain criteria. For example, I have:
Column A Column B
01-1000 5
02-1001 7
01-1002 6
02-2000 10
and I would like to sum data in column B if last 4 digits of data in column
A is less then 2000. If everything done right, answer should be 5+7+6=18. Any
suggestions? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518825
How do I intergrate lines added so they follow in descending order
I am adding style nos with descriptions and prices and want to be able to
have them update and intergrate with existing ones already on my spreadsheet.
How do I format the rows, columns or cells so that all the information will
move and not just the style nos? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518823
Get rid of $ signs in formulas
When I am linking one workbook to another, the default formula always has $'s
before and after the Column id. How do I link without having the $'s added?
--
cmatthes Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518811
NON SEQUENTIAL DATE RANGES
Is there a way to split out non-sequential dates in a column of dates? For
example I have listed for a single client service dates of Jan 1-5, Mar 3-11,
July 5-10 and then would have the non sequential dates for other clients. I
need a simple blank line (or any other way of indicating) when a client has
multiple admissions which will be known by the fact they have multiple
non-sequential ranges of dates in a column. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518810
Calculating dates of holidays
Hello,
I'm trying to dynamically calculate the dates that certain holidays
would fall on based on the year. Some of them are easy, because they
are the same date every year. However, there are some that fall on
different dates...i.e. Thanksgiving (in the U.S.) being the 4th
Thursday in November. I'm sure I can write a UDF that would do this,
but I like to avoid doing that when native functions will suffice.
However, I can't seem to figure a way to do this using what's already
there.
So, for instance, if I have 2008 in, say, A1, then the formula to
calculate Thanksgiving should return 11/27/2008. If I change the year
in A1 to 2012, then the formula would return 11/22/2012.
If it's possible, does anyone have any suggestions?
Excel 2003, SP2, Analysis ToolPak
XPPro, SP2
Thanks to all.
Mike Lee
Coppell, TX, USA Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518804
MACRO for Matching Two List
I would like to create a file of data that has quanties used from one time
period. Then, periodically take another file of the same items from another
time period and bump it up against the first one. I want to match the items
and put the usage quantities side by side. Then calculate a variance of the
change in quantities and sort from hi to lo. I know how to do the basics but
how to write a macro so that I take the new file and just do the bumping up
against automatically.
--
NLRHU91 Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518803
Protected Workbook, no comments?
I have cells unlocked in a protected workbook, those using the unlocked cells
need to be able to add comments but it is not an option. How do I allow
comments?
Thanks! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518802
Create macro with button in excel spreadsheet
I'm trying to create a macro in an excel workbook and have a button so that
when a customer completes the form, they hit the macro button and it creates
an EMail and populates the subject line. The folks who use the form download
it from my company's internal portal; all users are internal to the company.
Note, I have a rule set up in Outlook so that these EMails automatically
route to another folder so they don't get "lost" in my general inbox. Users
send the excel attachment to me OK, but the subject line is usually missed,
even though I have mistake-proofed it as best I can. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518801
Excel Function - Date evaluation
I want another cell to evaluate if there is a date (of any kind) in another
cell. If there is then I want the formula to place the date minus 30 days.
If there's not, then I want it to say "NA".
Ex:
Cell A4 can have, "OK", "Overdue", or physically input a date. Cell A5 will
be A4-30, if there is a date. If there is not then A5 will read "NA".
Thanks Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518796
Help with combining lists
I'm pretty new at this, but I'm having trouble with something. I have 2
lists that each have item numbers in them. I'm trying to take out all the
item numbers that appear in both lists so that what I'm left with is a few
numbers that are only in one list or the other. I can't figure out how to do
this because all the "remove duplicate" tools and formulas leave one of the 2
duplicates. I need to remove both of them. Does anyone have an idea of how
to do this?
The only way I can figure out is to paste both of the lists in one file, do
Conditional Formatting for the duplicates, and then pull out the ones that
don't have the special formatting. There has to be an easier way!
Thank you in advance! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518795
if statement syntax help
I have been reading a lot about conditional formatting. I have cells in D2
that I want highlighted in yellow if g2>5 and i2>69%. If g2 and i2 do not
both meet the criteria, I don't want anything to happen to d2. I'm assuming
I can use conditional formating for that, but how do I write it? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518785
Generate list
Im trying to simplify shopping lists in my home. I have a basic list that we
can complete weekly. how can i generate from this a list that will only
include items and stores that have to be shopped at? I am primarily looking
for a list that the column headers will be source and the row headers just
the items that have a value in the quantity column with the quantity showing
under the proper column.
So that a list that starts like this:
Item Source Quantity
Alaskan Salmon Trader Joes
Almond Milk Whole Foods 1
Apple Cider Smart & Final 3
Apple Juice Smart & Final 7
Apples Ralphs
will generate a list that looks like this:
Whole Foods Smart & Final
Almond Milk 1
Apple Cider 3
Apple Juice 7
Thank you. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518784
Pivot Table data
I'm not sure if this is the correct forum for this but here goes.
I have a pivot table that draws from several pages. when I click on the
values to find out what makes the values I get a second sheet that has:
ROW Column Value Page1
Item number Cumber title a date Item3 (or Item1, or
Item2...you get the idea)
How do I change the "Item#" to the actual sheet it came from.
if I need to explain more please let me know.
Thank you Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518778
Conditional Sum of cells
Hi
I have data in Cell A1,A2, C1,C2 E1,E2
A C E
10 15 12
1 0 1
I WANT THE SUM OF CELLS A1 & C1 ONLY IF THE VALUE IN A2 AND C2 IS NOT ZERO
THIS IS AN EXAMPLE. ACTUALLY THE NUMBER OF CELLS THAT I NEED SUM OF ARE MORE
THAN 20 IN THE SHEET. BUT THEY ARE NOT IN ORDER Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518776
Separating Strings
I'm sure this will come as a really simple question to the rest of you, but I
have a column with names in it; I want to divide them into first and last
names. How can I do this?
I started with finding the space in between the first and last names, like
this:
SEARCH(" ",A2)
Then when I got the number of the space, I started a conditional statement:
IF(SEARCH(" ",A2)>0, . . . .)
But that's where I lose my line of thinking.So I'm telling it that if it
finds a space, then I want it to return everything BEFORE the space to one
column and then I need another function to return everything AFTER the space
in another column.
TIA for your help! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518775
change formula in a shared worksheet without losing change history
Hi Rick,
Thank you for calling my attention for the lack of details with my question.
My question is:
I posted a shared worksheet in a shared drive that others could update with
their changes and corrections. However, getting back to the shared worksheet,
I realized I had to change an array formula in one of the tabs. When I tried
to change the formula, I receive a message saying that it is one of the
things that I am not allowed to do without losing the change history (
records of people's ID, time & date they went to the file and the changes
they made). One way I thought of doing is to save the old file in a place
others do not have access to, & re-create the file with a new file name.
That will allow me now to change my formulas. However, I will lose the change
history in the new file and I do not want that. I know that all the changes
are there but I want to be able to see the change history just in the one new
file without having to bring out the previous file. I hope this clears my
dilemma. Thank you Rick for your suggestion. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518773
Printing with ""
I have a chart and it uses a forumla to retrieve data, and if no data is
available it sets the cell to "". My problem is that when I try to print the
worksheet, it prints an extra blank page (because of all the "").
My forumals look something like:
=if(DATA,GET THE DATA,"")
Is there a way to prevent an extra page from being printed without using
Print Area? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518770
Expansion of COUNT if Question
I have two large columns of data, one with numbers and the other is verbal. I
need to write a code in a specific cell that will SUM of a group of those
numbers if the adjacent column meets a certain Criteria.
For Example
Column 1 Column 2
A 2
B 5
C 7
A 8
A 4
B 3
C 2
So i need the code in the cell to Sum up all the numbers in column 2, that
have a A in column 1.
How can i do this??
Thanks in Advance for you help!!!
-Adam Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518766
Adding duplicates...
I need help figuring out a formula to add duplicates as follows:
I have 3 months of figures with duplicate names on each month. I need to
add these amounts to a quarterly sheet. I want the names on the quarterly
sheet to show once, and with the totals for the 3 months. I'm hoping to not
also have to use a formula indicating a name in there, if that makes sense:
i.e
January;
Janet 5
Carol 10
February:
Bill 4
Janet 8
March:
Jon 11
Carol 14
Quarterly:
Janet 13
Carol 24
Bill 4
Jon 11 Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518764
Insert Graphics File into Worksheet via Formula
<<Previously posted in m.p.e.charting under "Graphics Filename via
Formula">>
I am trying to programmatically generate a 9-panel chart similar to
those you see in stock prospectuses, with the horizontal displaying
"Investment Style" and the vertical displaying "Market Cap". Each of
the 9 panels within the grid represents a different combination of
the
horizontal vs. vertical values.
I have a very kludgy solution using conditional formatting on a grid
of 9 individual cells to turn one of them on and leave the rest
neutral, but this solution is hard to maintain and expand for more
source data. I would like to take nine graphic files, one for each
possible highlighted square position, and use a formula to derive a
value that would be used to display a graphic with that filename. For
instance, for "Growth" and "Large", if the formula produced a result
of "GL", the file named "GL.jpg" (or GIF or PNG) would appear in a
certain cell (each of the nine graphic files would be identical in
dimensions). If the source data changes, the graphic would
automagically be changed, too. If two rows of data produce the same
combination of horizontal vs. vertical values, they would each
display
the same graphic file.
How do I do this in Excel (2003, preferably)?
advTHANKSance! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518753
correct formula
(cell1)1254-(cell2)987=267 cell2)267/(cell1)1254= answer
I am looking for one formula to pick up this answer please help.
so 1254 minus 987 equals 267, 267 divide by 1254 = answer %
This is all revenue and budget based 987 is the actual 1254 is the budget
and i am trying to add two colums for growth vs budget and growth vs prior
year. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518751
calculate percentage of numbers <= zero in a given range
I need to calculate the percentage of numbers, within a range of
cells, that are equal to zero or less than zero.
Simple example: numbers are 8, -1, 8, 8, 0, 0
The percentage of numbers that are equal to zero or less than zero
would be 3/6 or 50%
How can I do this in Excel? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518740
Vlookup on Dates
I have a table containg start dates in column a and end dates in column b.
The start date for the second (and subsequent) record is the end date for the
first record plus one day e.g. if the end date was 31/12/2007 then the next
row would show a start date of 01/01/2008.
How can I get a vlookup function to correctly identify the row in which the
target date falls between a start date and end date (both on the same row).
Once the correct row has been selected the vlookup will need to lookup the
contents of a cell to the right of the indexing columns.
I have tried this by only using the end date and find that the function
selects the row before the row containing the relevent start and end dates.
Thanks for any help/advise. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518729
Extracting data from Outlook to Excel using VBA
Hi,
I need to get data from messages in Outlook to fill fields within Excel
worksheet using VBA. Can anyone suggest me a website or ebook where i can
obtain information?
Thanks in advance
HBruno Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518728
IF
I need to create a formula for the following scenario ... any help would be
appreciated!
If value in cell is 3, then multiply another cell by .376, If 4, multiply by
.668, and so on. For instance:
A1 = 3
B1 = 4.5
Formula Cell C1 should return 1.69
OR
A1 = 4
B1 = 4.5
Formula Cell C1 should return 3.01
Can this be done? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518721
Annual Leave automatic calculation
Hi
I am trying to do annual leave calculation what i need is an automatic
calculation for the following
The rule is
Every employee is entitled to a fully paid annual vacation after the
completion of 3 months
1 to 5 years of consecutive service fifteen (15) days per year
5 to 10 years of consecutive service seventeen (17) days per year
10 to 15 years of consecutive service nineteen (19) days per year
B C
1 Name Marcel Fouad Breidy
2 Staff Number 50005
3 Position ST1
4 Starting Date 01-May-03
5 Total Working Days =+(NOW()-C7)/30/12 =5.31 years
D
7 Annual Leave pr year Days Taken Days Remaining
8 17 61=D11
19.67=A10-D11
9 Total Days allowed
10 =5*15+(C8-5)*17
11 From Day Inclusive To Day
Exclusive Total Annual Leave
A8 =The formula that i am using to find the Annual Leave pr year is
the following
=IF(TODAY()-C7>10*365,19,IF(TODAY()-C7>5*365,17,IF(TODAY()-
C7>0.25*365,15)))
What i want is an automatic calculation for the total days allowed
instead of mannually putting the formula i want it to deduct the
previous 5 years as pr the rules =5*15+(C8-5)*17
What is the formula that allows me to do that ?
Thank you in advance
Tia Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518720
How to Create Macro to Increase Cell Value to one and Print Worksheet
We are trying to automate the printing of a worksheet. In sheet
"Jobs" of the workbook it has consecutive numbers in column A (1,
2,3 ...) and is variable. We would like to automate the insertion of
each consecutive number from this column A into cell C6 of the Summary
sheet, and print out the Summary sheet, until the last number is
reached in column A.
Example: Column A has numbers 1-32. The macro will copy 32 times
(numbers 1 through 32) into cell C6 and each time print the Summary
sheet out. (by copying the number into C6 it will activate other
formulas...)
Thanks so much for anyone who can assist! Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518719
how do I set up or adjust my column chart?
I am creating a shipping schedule template and have 9 weeks that I ship
products. I would like to show each customer how many units they receive per
week.
My template is set up for 9 ship weeks. Here is an example for 3 shipments.
0 means no product is being shipped.
Row 1: Oct 6, Oct 13, Oct 20, 0,0,0,0,0,0 (total 9 weeks)
Row 2: 233, 545, 102, 0,0,0,0,0,0 (number of units shipped that week)
Now most of my customers only receive 2 or 3 shipments and only a few take
as many a 8 or 9 shipments.
How can I set up a column chart so that I only see 3 columns when I ship 3
weeks and not all 9 weeks where only the first 3 weeks show a value and the
other weeks are all 0?
Thanks. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518718
Pivot Table Question
Hi.
When I create a pivot table, the last row before the "Grand Total" row is
titled "(blank)." Even if there are no blank items within the pivot table,
Excel still produces a "(blank)" row in the pivot table. Is there any way to
getting rid of or automatically defaulting Excel to not produce the "(blank)"
row? (Without, of course, hiding a column.)
Thank you. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518712
MCONCAT
Hi All,
I have a list of hand typed subjects in column B2 thru B20
I use MCONCAT to group them into cell B1
I now have the need to bring in the subjects via VLOOKUP.
Obviously, MCONCAT will not group formulas.
Any suggestions?
Thanks for your help in advance. Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518711
Use tab name in Vlook Up
I have a formula that looks up data in a sheet using Vlookup, every week the
formula changes as the week number changes.
The tab to look for data has a tab name of wk## and thus the forumla for
week 28 looks like this
=VLOOKUP(O2,wk28!A4:L3000,12,FALSE)
How can I substitue the wk28 tab number in the formual for a cell reference
that contains the text "wk28" ?
This will save me modifying forumlas on weekly basis.
Thanks Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518707
Entering changing months with a cell
Cells D1:AJ1 have years 2006:2038. Cells C2:C13 have Jan thru Dec. Cell A4
has the array formula
=LOOKUP(9.999999999999E+307,INDEX(D2:AH13,0,MAX(IF(D2:AH13<>"",COLUMN(D2:AH13)-COLUMN(D2)+1))))
,showing the last figure entered in the table. Note: I had to push
Ctrl+Shift+Enter to execute the formula. How do I enter into cell BC16 the
Month corresponding to the last figure entered in D2:AH13 from column C? Tag: Cannot insert worksheet in exel - not available in insert menu Tag: 518706