Free Microsoft Excel 2013 Quick Reference

Fill series Q1, Q2, Q3, Q4

I am attempting to fill a series from A1-R1. I have not noticed it until
today but when I pull this series down past A4-R4, the "Q" series stars
duplicating the Q1-Q$ and will not follow the same series format...how can I
fix this?


Post your answer or comment

comments powered by Disqus
How do I make first quarter (Q1) = Oct, Nov, Dec instead of being Q4? Please help. I would like to calculate quarters on my report by quarter number for calendar year & quarter number for a fiscal year. I have several years. Thank you

I want:
Oct, Nov, Dec =Q1
Jan, Feb, Mar = Q2
Apr, May, Jun = Q3
July, Aug, Sept = Q4

Using excel 2007: I have a table full of data which includes column headings (let's call them Q1 Q2 Q3 Q4) My rows have type codes North South East West. Cells within the table are filled with numbers. I have defined the top row and left column names using 'Create from Selection' under the formulas tab. Everything OK so far... I can type =Q1 North in a different cell and get the desired intersecting cell value. However, I'd like to instead add a formula that will allow me to point and click in two different cells which contain the terms "Q1" and separately "North", perhaps on another page. This does not work. Any thoughts or suggestions are welcome. Thank you in advance. Sorry, I am unable to show my spreadsheet from the office.

Regards,

jim

Is there a way to show 2 series as 2 stacked columns on 1 chart?

For example -

x-axis = quarters of the year: Q1, Q2, Q3, Q4

left y-axis = projected sales by region as a stacked column

right y-axis = actual sales by region as a stacked column

Regions = America, Europe, EMEA, APAC

This chart can help get a quarterly comparison of projections vs actuals by
quarter and region.

Thanks!

Hi everyone! Does anyone know an excel macro that would enable me to put row data into a single column? I basically have quarterly data for 5 years with each row containing the quarterly data for each year (row). I just want to make the data into a time series. So it's something like: (ignore the dots)

Year ...... Q1 ....... Q2 ....... Q3 ....... Q4
1990 ...... 2 ......... 5 ......... 3 ......... 9
1991 ...... 8 ......... 1 ......... 4 ......... 6

and I want a macro that would put the row data into a single column so that it would look like:

2
5
3
9
8
1
4
6

Any assistance would be greatly appreciated. Thanks!

Probably an easy answer but i cant figure it out for anything

I want to show a "summed total" data label for a stacked column chart.

For example, i am looking at sales tax revenues. The years are broken out on the bottom of the chart (x axis). i have 4 data series, which are broken down by quarters (Q1,Q2,Q3,Q4).

Now, i want to show visually the difference in the quarters, but have a data label showing the "summed total" for the entire year.

any help is much appreciated.

thanks in advance,
greg

Hi,

I have 5 sheets in this workbook with Summary being the 1st sheet and 1 sheet for each quarter - Q1, Q2, Q3 & Q4.

Q1 to Q4 sheets all have data in them. I need to populate the data in the summary sheet based on certain dates provided.

For e.g the summary sheet only has dates from 10/4 to 10/8 that need to be populated, but this is subject to change. How can i have the data filled into the cells below the dates, just by changing the data above?

I at first thought that offset and indirect might work but am not able to put them in together.

Any help would be greatly appreciated.

Hello,

New to forum and visual basic. I've gotten by using formulas with in cells until now.

Workbook contains two sheets( "Data", "Summary" )

The "Data" sheet contains four columns with headings (Q1, Q2, Q3, Q4)

For example under heading "Q1" I have (pizza, cake, "blank cell", hamburger)

In the "Summary" sheet, I'm trying to pull the column data from "Q1" minus blank cells and insert (pizza, cake, hamburger) under the heading, "What are you Hungry for?". I can get the data over using a True/False helper column in "Data", but I can't figure out how to insert rather than paste the information.

I need to be able to do this with each column (Q1, Q2, Q3, Q4). I'm really not even sure if what I'm attempting is even possible because there could be one answer or 100 answers under "What are you hungry for?" and excel would need to know where to insert the answers for Q2 after inserting Q1 data.

I'm sure who ever reads this is extremely confused now because I've already lost myself.

I'm attaching a sample of what I'm trying to do in hope that it helps.

Thanks,
Mike

sample.xls

I have five sheets named 'Yearly,Q1,Q2,Q3,Q4'. currently my code only update one worksheet at one time into the mySql database. i want it to get all worksheets to be done at one go. i've tried to implement 'For loop' but it just run on the active worksheet only. is there anything need to be added into my coding to make it works? here's my code;

	VB:
	
 wbkFirst.Worksheets 
     
    Do While IsEmpty(wksSheet.Cells(lastRowCom, "B").Value) = False 
         
        stockCodeExist = False 
         'Sheet Yearly
        stockCodeCom = Trim(wksSheet.Cells(lastRowCom, "B").Value) 
        marketCapCom = Trim(wksSheet.Cells(lastRowCom, "C").Value) 
        revenueCom = Trim(wksSheet.Cells(lastRowCom, "D").Value) 
        TotLiability = Trim(wksSheet.Cells(lastRowCom, "E").Value) 
        curLiability = Trim(wksSheet.Cells(lastRowCom, "F").Value) 
        tradeReceivable = Trim(wksSheet.Cells(lastRowCom, "G").Value) 
        inventory = Trim(wksSheet.Cells(lastRowCom, "H").Value) 
        shareholderEqyCom = Trim(wksSheet.Cells(lastRowCom, "I").Value) 
        totLiabToSE = Trim(wksSheet.Cells(lastRowCom, "J").Value) 
        currentAss = Trim(wksSheet.Cells(lastRowCom, "K").Value) 
        fixedAsset = Trim(wksSheet.Cells(lastRowCom, "L").Value) 
        operatingExpenses = Trim(wksSheet.Cells(lastRowCom, "M").Value) 
        ClosingPrice = Trim(wksSheet.Cells(lastRowCom, "N").Value) 
        priceEarningRatio = Trim(wksSheet.Cells(lastRowCom, "O").Value) 
        earningPerShare = Trim(wksSheet.Cells(lastRowCom, "P").Value) 
        salesToTotalAss = Trim(wksSheet.Cells(lastRowCom, "Q").Value) 
        totalAss = Trim(wksSheet.Cells(lastRowCom, "R").Value) 
        operatingCashFlow = Trim(wksSheet.Cells(lastRowCom, "S").Value) 
        peRatio = Trim(wksSheet.Cells(lastRowCom, "T").Value) 
        altmanZ = Trim(wksSheet.Cells(lastRowCom, "U").Value) 
        cashNeqvalent = Trim(wksSheet.Cells(lastRowCom, "V").Value) 
        EbitCom = Trim(wksSheet.Cells(lastRowCom, "W").Value) 
        sharesOutstdgCom = Trim(wksSheet.Cells(lastRowCom, "X").Value) 
        netAttributableCom = Trim(wksSheet.Cells(lastRowCom, "Y").Value) 
        incStock = Trim(wksSheet.Cells(lastRowCom, "Z").Value) 
        netInvest = Trim(wksSheet.Cells(lastRowCom, "AA").Value) 
        retainedPOL = Trim(wksSheet.Cells(lastRowCom, "AB").Value) 
        ReOTA = Trim(wksSheet.Cells(lastRowCom, "AC").Value) 
        ReOE = Trim(wksSheet.Cells(lastRowCom, "AD").Value) 
        AvergShares = Trim(wksSheet.Cells(lastRowCom, "AE").Value) 
        netPOL = Trim(wksSheet.Cells(lastRowCom, "AF").Value) 
        DebtEqy = Trim(wksSheet.Cells(lastRowCom, "AG").Value) 
        paidUpCapital = Trim(wksSheet.Cells(lastRowCom, "AH").Value) 
        fiyeMonth = Trim(wksSheet.Cells(lastRowCom, "AI").Value) 
        yearIdCom = wksSheet.Cells(1, 2) 
        periodIdCom = wksSheet.Cells(1, 3) 
         
         'check company financial exist
        Set rs = New ADODB.Recordset 
        sqlStr = "SELECT year_id, period_id, stock_code FROM company_financial WHERE year_id = '" & yearIdCom & "' AND
period_id = '" & periodIdCom & "' AND stock_code = '" & stockCodeCom & "' " 
        rs.Open sqlStr, conn, adOpenStatic 
         
         
        If rs.EOF Then 
             'record not exist in DB
            recordExistCom = False 
        Else 
            yearIdCom = rs.Fields("year_id") 
            periodIdCom = rs.Fields("period_id") 
            stockCodeCom = rs.Fields("stock_code") 
            recordExistCom = True 
        End If 
         
         
        rs.Close 
        Set rs = Nothing 
         
         
         
         
        If recordExistCom = False Then 
             'insert new company
            If stockCodeCom  "" Then 
                 
                sqlStr = "INSERT INTO company_financial " 
                sqlStr = sqlStr & "(year_id, period_id, stock_code, receivable, revenue, total_liabilities,
shareholders_equity, total_liabilities_to_shareholders_equity, current_liabilities, total_current_assets, net_attributable,
inventories, fixed_assets, operating_cost, net_cash_operation, price_close, pe, eps, pe_relative_sector, altman_z_score, " 
                sqlStr = sqlStr & "paid_up_capital, net_profit_or_loss, dec_stock, dec_debtors, inc_creditors,
net_investments, cash_and_equivalents, retained_profit_or_loss, ebit, mkt_cap, sales_to_assets, rota, roe, debt_to_equity,
average_shares, total_assets, shares_outstanding, report_period_end_mth," 
                sqlStr = sqlStr & "created_by,created_date)" 
                sqlStr = sqlStr & " VALUES " 
                sqlStr = sqlStr & " ('" & yearIdCom & "', '" & periodIdCom & "', '" & stockCodeCom & "', '" &
CDbl(tradeReceivable) & "', '" & CDbl(revenueCom) & "', '" & CDbl(TotLiability) & "', '" & CDbl(shareholderEqyCom) & "', '" &
CDbl(totLiabToSE) & "', '" & CDbl(curLiability) & "', '" & CDbl(currentAss) & "', '" & CDbl(netAttributableCom) & "', '" &
CDbl(inventory) & "', '" & CDbl(fixedAsset) & "', '" & CDbl(operatingExpenses) & "', '" & CDbl(operatingCashFlow) & "', '" &
CDbl(ClosingPrice) & "', '" & CDbl(priceEarningRatio) & "', '" & CDbl(earningPerShare) & "', '" & CDbl(peRatio) & "', '" &
CDbl(altmanZ) & "'," 
                sqlStr = sqlStr & "'" & CDbl(paidUpCapital) & "', '" & CDbl(netPOL) & "', '" & CDbl(incStock) & "', '" &
CDbl(netInvest) & "', '" & CDbl(netInvest) & "', '" & CDbl(netInvest) & "', '" & CDbl(cashNeqvalent) & "', '" &
CDbl(retainedPOL) & "', '" & CDbl(EbitCom) & "', '" & CDbl(marketCapCom) & "', '" & CDbl(salesToTotalAss) & "', '" &
CDbl(ReOTA) & "', '" & CDbl(ReOE) & "', '" & CDbl(DebtEqy) & "', '" & CDbl(AvergShares) & "', '" & CDbl(totalAss) & "', '" &
CDbl(sharesOutstdgCom) & "', '" & fiyeMonth & "'," 
                sqlStr = sqlStr & "'fistconv','" & Format(Now(), "yyyy-MM-dd HH:mm") & "')" 
                 
                conn.Execute sqlStr 
            End If 
            lastRow = lastRow + 1 
            Range("C3").Value = lastRow - 5 
             
        Else 
             
            sqlStr = "UPDATE company_financial SET " 
            sqlStr = sqlStr & " receivable = '" & CDbl(tradeReceivable) & "'," 
            sqlStr = sqlStr & " revenue = '" & CDbl(revenueCom) & "'," 
            sqlStr = sqlStr & " total_liabilities = '" & CDbl(TotLiability) & "'," 
            sqlStr = sqlStr & " shareholders_equity = '" & CDbl(shareholderEqyCom) & "'," 
            sqlStr = sqlStr & " total_liabilities_to_shareholders_equity = '" & CDbl(totLiabToSE) & "'," 
            sqlStr = sqlStr & " current_liabilities = '" & CDbl(curLiability) & "'," 
            sqlStr = sqlStr & " total_current_assets = '" & CDbl(currentAss) & "'," 
            sqlStr = sqlStr & " net_attributable = '" & CDbl(netAttributableCom) & "'," 
            sqlStr = sqlStr & " inventories = '" & CDbl(inventory) & "'," 
            sqlStr = sqlStr & " fixed_assets = '" & CDbl(fixedAsset) & "'," 
            sqlStr = sqlStr & " operating_cost = '" & CDbl(operatingExpenses) & "'," 
            sqlStr = sqlStr & " net_cash_operation = '" & CDbl(operatingCashFlow) & "'," 
            sqlStr = sqlStr & " price_close = '" & CDbl(ClosingPrice) & "'," 
            sqlStr = sqlStr & " pe = '" & CDbl(priceEarningRatio) & "'," 
            sqlStr = sqlStr & " eps = '" & CDbl(earningPerShare) & "'," 
            sqlStr = sqlStr & " pe_relative_sector = '" & CDbl(peRatio) & "'," 
            sqlStr = sqlStr & " altman_z_score = '" & CDbl(altmanZ) & "'," 
            sqlStr = sqlStr & " paid_up_capital = '" & CDbl(paidUpCapital) & "'," 
            sqlStr = sqlStr & " net_profit_or_loss = '" & CDbl(netPOL) & "'," 
            sqlStr = sqlStr & " dec_stock = '" & CDbl(incStock) & "'," 
            sqlStr = sqlStr & " dec_debtors = '" & CDbl(netInvest) & "'," 
            sqlStr = sqlStr & " inc_creditors = '" & CDbl(netInvest) & "'," 
            sqlStr = sqlStr & " net_investments = '" & CDbl(netInvest) & "'," 
            sqlStr = sqlStr & " cash_and_equivalents = '" & CDbl(cashNeqvalent) & "'," 
            sqlStr = sqlStr & " retained_profit_or_loss = '" & CDbl(retainedPOL) & "'," 
            sqlStr = sqlStr & " ebit = '" & CDbl(EbitCom) & "'," 
            sqlStr = sqlStr & " mkt_cap = '" & CDbl(marketCapCom) & "'," 
            sqlStr = sqlStr & " sales_to_assets = '" & CDbl(salesToTotalAss) & "'," 
            sqlStr = sqlStr & " rota = '" & CDbl(ReOTA) & "'," 
            sqlStr = sqlStr & " roe = '" & CDbl(ReOE) & "'," 
            sqlStr = sqlStr & " debt_to_equity = '" & CDbl(DebtEqy) & "'," 
            sqlStr = sqlStr & " average_shares = '" & CDbl(AvergShares) & "'," 
            sqlStr = sqlStr & " total_assets = '" & CDbl(totalAss) & "'," 
            sqlStr = sqlStr & " shares_outstanding = '" & CDbl(sharesOutstdgCom) & "'," 
            sqlStr = sqlStr & " report_period_end_mth = '" & fiyeMonth & "'," 
             
             
            sqlStr = sqlStr & " update_by = 'fistconv'," 
            sqlStr = sqlStr & " update_date = '" & Format(Now(), "yyyy-MM-dd HH:mm") & "'" 
            sqlStr = sqlStr & " WHERE year_id = '" & yearIdCom & "' AND period_id = '" & periodIdCom & "' AND stock_code = '"
& stockCodeCom & "'" 
             
            Debug.Print sqlStr 
             
            conn.Execute sqlStr 
             
        End If 
        lastRowCom = lastRowCom + 1 
        Range("E3").Value = lastRowCom - 5 
    Loop 
     
Next wksSheet 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am analyzing my Questionnaire where i want to calculate the results using Excel.

Supposedly there are 5 Multiple Choice Questions asked to the different set of peoples.

Sno Q1 Q2 Q3 Q4 Q5 TOTAL
1 A B C D E ?
2 A C B D E ?
3 B D D E A ?
4 A C A D A ?
5 A B C D A ?

I want to calculate that how out 5 questions, how many correct answers has been given by person 1?
If the answer to question 1 =a, q2=a, q3=c, q4=b,q5=d then how many answers in total has the person 1 has given correct...??

Please help me in this. I am trying to make a formula for this.

Hi
I have a drop down box in T3, with just 4 choices Q1 Q2 Q3 Q4.The criteria for the drop down is in cells AD3 AD4 AD5 and AD6

I want to hide Column WXY if Q1 is selected, VXY if Q2 is selected, VWY Q3, VWX Q4. I searched the forum and can find similar scenerios but nothing to fit my needs.

Can anybody help?

This is my project, I can get so far but then become unstuck.

I have a "child" workbook with 5 pages, Q1 (for quarter 1) Q2 Q3 Q4 and year. The year is simply a summary of Q1 Q2 Q3 Q4. On each sheet are 13 weeks which are the weeks of that quarter. So far so good. There are 10 people using a "child" workbook and one "parent" copy that requires the information in the child books to be imported in.
I thought that copying the current child sheet lets say that is Q4 into the master, so I have 10 imported sheets would be a good place to start. Having got there the master sheet has a summary page which is a summary of the 10 imported pages. Of course the pages are named Q4(copy1) on so on, so Change the names to Q1 1, or Q1 persons name. Now the master sheet is looking as though it has all the info required to complete the master summary

I can import the child sheets into the master, using =indirect() keep th references stable, if I delete the existing child sheets ONLY before importing I think I should be ok.

As a start, am I approaching this in the right way???

Hi,

The scenario is a quarterly forecast update.

In my first cell I have a formula which returns a value from a row somewhere else depending on the date entered in a cell somewhere else. This might not be important but the formula is:

=IF(AND(Summary!A1> DATE(2011,12,31),Summary!A1< DATE(2012,3,31)),AV11,IF(AND(SUMMARY!A1> DATE(2012,3,31),Summary!A1< DATE(2012,6,30)),AW11,IF(AND(SUMMARY!A1> DATE(2012,6,30),Summary!A1< DATE(2012,9,30)),AX11,IF(AND(SUMMARY!A1> DATE(2012,9,30),Summary!A1< DATE(2012,12,31)),AY11))))

What I am struggling to do is put a formula in the cell next to the first cell which returns the value next to the one in the row that is being returned in the first cell. So whatever ends up in the first cell I need the value next to that (in the row of data). I then need the same thing for the 3rd cell and 4th cell.

Even more confusing is in the 5th, 6th, and 7th cells I need values for a full year. So in the 5th cell it needs to return the sum of the 4 values next to whatever is in the 4th cell. The 6th cell will need to be the same starting with the the one after the last cell that was used in the sum of the cells in the 5th cell. And the same for the 7th cell but with the 6th cell.

I have tried HLOOKUP and multiple IF's but nothing is working...

This is where I'm trying to get the formulas. The cell with 42 is what I referred to as my first cell (which contains the formula I pasted). On my sheet it is T24

FORECAST Q1 Q2 Q3 Q4 Yr+1 Yr+2 Yr+3 42

The 'Total Man month' row (below) is the row I referred to where the values are coming from. The 42 here (AV11) is currently in T24 through the formula I pasted.

Copy in border from PH Business Case: Headcount: UK 4 8 11 14 18 23 25 25 19 17 15 13 Czech Korea 1 3 8 8 8 9 8 5 Japan US Total Man month 10.89 24.375 33.75 42 58.875 77.25 99 99 78 77.25 71.1 53.85

Hopefully that makes sense, please let me know if further clarification is needed.

Any help is much appreciated

here's my problem. I need to copy multiple sheets into the template accordingly. well, i manage to copy for the first sheet only. i've tried to modify the code to copy multiple sheets at one time and i couldnt get it right. here's the code for copying the first sheet into the template. i have to specify the column as i just need certain column to be copied for that particular sheet.

i have five sheets on the first file; Year, Q1, Q2, Q3, Q4
the template consist of 5 sheets as well; Yearly, Q1, Q2, Q3, Q4
here is my code;


	VB:
	
 Copy() 
    Dim wbk As Workbook 
    Dim strFirstFile As String 
    Dim strSecondFile As String 
     
    strFirstFile = "C:Documents and SettingsuserMy DocumentsFiST MacData.xls" 
    strSecondFile = "C:Documents and SettingsuserMy DocumentsFiST MacFiST_data_template.xls" 
     
    Set wbk = Workbooks.Open(strFirstFile) 
    With wbk.Sheets("Year") 
        .Range(.Range("A5:AJ5"), .Range("A65536").End(xlUp)).Copy 
         
    End With 
     
    Set wbk = Workbooks.Open(strSecondFile) 
    With wbk.Sheets("Yearly") 
         
        Sheets("Yearly").[B65536:AK65536].End(xlUp)(2).PasteSpecial Paste:=xlValues 
         
    End With 
     
    Application.DisplayAlerts = False 
    wbk.SaveAs "C:Documents and SettingsuserMy DocumentsFiST MacFISTdb.csv" 
    Windows("Data.xls").Close 
    MsgBox "FiST Database Updated", vbOKOnly, " FiST" 
     
     
    Application.DisplayAlerts = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thank you for your help. cheers

Hi,

I have a data base in Accesss. I import it monthly into Excel to perform analysis. In column 2 I have the Quarter listed as 1, 2, 3, 4. Every time I click refresh to import the new records I owuld like those 1,2,3, and 4s to be appended with the letter Q at the begginging. So the whole column would have Q1,Q2,Q3,Q4 instead. I want this aciton to perform in conjunction with the data import each time I refresh the data so that it is done automatically. I have bar charts and graphs that link to the data and I want the labels to read Q1,Q2,Q3,Q4 instead of just 1,2,3,4, which dosn;t look very good. The idea is that it would be an automated action. Perhaps a VBA code or a macro would work. Perhaps it could be tied to 1 button called "Refrresh" that would do the import and at the same time append the Q's to this column. Can someone please help. Any codes would be much appreciated. I need help as soon as possible as I am trying to build a dashboard to analyse this data by the end of the week!

Thanks

I have date formatted cells in column A (eg. 20-Nov-03). I want another column to show their corresponding annual quarter (eg. Q1, Q2, Q3, Q4)--- not based on the calendar year, but our fiscal year: Q1 is Jun, Jul, Aug. Q2 is Sep, Oct, Nov, and so on.

I am trying to come up with a formula that will help me forecast cost savings, by quarter based on our fiscal year (Oct. 1 to Sept 30). I am allowed to take 12 months of savings from an event date. I have the list of event dates and the total annual savings per event. How can I show the amount of savings per quarter for each line item? I played around with the various date functions, vlookup, and various logical functions (IF AND, IF OR), but can't figure out how to allocate the savings percentage for each quarter. Saving are limited to 12 months, but can be span fiscal years. I was trying to get the accuracy down to days, so the amount would vary a bit depending upon how far you were in the quarter with respect to the event date. Any ideas??

event date| annual savings |Q1|Q2|Q3|Q4
10/1/03 100 25 25 25 25
2/9/04 200 ?? ?? ??

Hi,
I have a workbook saved as a template, it contains 15 work sheets, they are numbered A1,A2,A3,Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,B1,B2. Not all of the Q numbered worksheets are always used. What I need is a macro to delete any Q numbered sheet with no data in cell C4. The A and B numbered worksheets must not be deleted.

Any ideas?
Thanks.

Hi there,

I am trying to convert take all the rows that start with Q1, Q2, Q3, Q4 and Cal 2009 to the bottom so I can then format the data. anyone know what would be the best way? Right now, I can only think of copy and paste but it seems like it is a very sloppy way of doing this.

thanks in advance.

******** ******************** ************************************************************************>Microsoft Excel - U.S..xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE67=
EFGHIJ67NYMEX Calendar Swap Settles68Month/YrCL FPHO FPHO CrackRB FPRB Crack69Nov'0867.972.029717.271.5778-1.7170Dec'0868.402.055717.941.5504-3.2871      72Jan'0968.862.090218.931.5759-2.6873Feb'0969.332.113719.451.6049-1.9274Mar'0969.822.124719.421.6349-1.1575Q1'0969.342.109519.261.6052-1.9276Apr'0970.342.127219.011.79745.1577May'0970.852.131218.661.81145.2378Jun'0971.342.138718.491.82045.1279Q2'0970.842.132418.721.80975.1780Jul'0971.842.156718.741.82444.7881Aug'0972.372.179219.161.82244.1782Sep'0972.902.204219.681.81643.3983Q3'0972.372.180019.191.82114.1184Oct'0973.442.226720.081.7164-1.3585Nov'0973.982.245720.341.7129-2.0486Dec'0974.502.264720.621.7179-2.3587Q4'0973.982.245720.341.7157-1.9188Cal  200971.632.166919.381.73791.3689Jan'1075.012.284220.931.7364-2.0890Feb'1075.502.298721.041.7634-1.4491Mar'1075.982.303220.751.7954-0.5892Q1'1075.502.295420.911.7651-1.3693Apr'1076.432.287719.651.95545.69OCEAN DIRECT 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In podcast 834, Bill showed how to spear data from multiple sheets into one, which is fantastic by its own merit.

However I have a further question, perhaps someone here knows the answer.

In Bill's example all sheets had similar names (Q1, Q2, Q3, Q4)...so the reference was somewhat simple (Q1:Q4)!

How could i do that reference when the sheets have different names (e.g. East, West, North, South, etc)?

I'm attempting to make a matrix calculate properly. The calculation is cumulative, with a new percentage used at certain thresholds:

******** ******************** ************************************************************************>Microsoft Excel - 2008 tap spreadsheet Test 3.xls___Running: xl97 : OS = Windows XP File Edit View Insert Options Tools Data Window Help AboutH65I65J65K65F66H66I66J66K66F67H67I67J67K67F68H68I68J68K68F69H69I69J69K69F70H70I70J70K70F71H71I71J71K71=
DEFGHIJK63    Cumulative total of the YTD Incentive Payment64Thresholds   Q1Q2Q3Q4650- $49,999Payout %:5%  $              1,526.58  $        1,754.69  $          1,754.69  $   1,754.69 66$50,000-$99,999Payout %:10%  $              3,053.17  $       3,509.38  $         3,509.38  $   3,509.38 67$100,000-$149,000Payout %:15%  $             4,579.75  $       5,264.07  $         5,264.07  $   5,264.07 68$150,000-199,999Payout %:20%  $              6,106.33  $        7,018.76  $          7,018.76  $   7,018.76 69$200,000-$249,999Payout %:25%  $             7,632.92  $       8,773.45  $         8,773.45  $   8,773.45 70$250,000-$299,999Payout %:30%  $              9,159.50  $      10,528.14  $        10,528.14  $  10,528.14 71$300,000+Payout %:35%  $           10,686.08  $     12,282.83  $       12,282.83  $ 12,282.83 sheet 1 (2) 
[HtmlMaker light Ver1.11] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Here's how the matrix would be used. Cells H65 through K71 calculate the cumulative payouts by quarter based on a persons sales. For example, if a person's sales for quarter 1 were $60K, they would receive an incentive of 5% against the first $49,999 and 10% for the next $20K. Once a threshold is reached all future incentives are calculated from dollar one (in this example, this individual would be paid 10% on all future sales until they reached $100K. They would then be paid 15%, etc.)

Right now the array H65:K71 simply applies a percentage against a total from another cell in the spreadsheet which is incorrect. The solution likely requires a complex IF/THEN statement, but I'm having a little difficulty putting it together. Any assistance from the community would be helpful. Thanks in advance for all contributions.

Hi

I have racked my brain (which is quite small!) to think of a way of doing this but have hit a brick wall

i have 2 sheets, one front sheet that gives an overview of the results and another which gives further brake down of that month.

i want to be able to have the front sheet collate the average scores of a questionaire that is broken up between 2 different types of people. below are examples of how i have it set out becuase i probably haven't explaind myself well! :-S

Front Sheet
MarchAprilMayAverage Temp ScoreAverage Con Score Monthly % Con to be re-employedMonthly % Temp to be re-employed

Monthly Sheet
NameCon or TempQ1Q2Q3Q4Average ScoreRe-employ?Ram SinghCon44444YesDavid LedgerTemp44444YesHugo CrosselyCon33333Yes

If someone can understand what i am saying, could anyone help please?


A

B

C

D

E

F

G

1

Q1

Q2

Q3

Q4

Joe

2

01/01/2008

04/01/2008

07/01/2008

09/01/2008

Tom

3

03/31/2008

06/30/2008

08/31/2008

12/31/2008

Jess

4

Date

Name

5

01/05/2008

Joe

Joe

6

01/06/2008

Tom

7

01/07/2008

Jess

8

01/08/2008

Joe

9

02/05/2008

Tom

10

02/06/2008

Jess

11

TOTAL

1

I am finding totals by a specific date range (Quarters, Q1, Q2...) and by a name. IE: How many times did Joe show up within Q1. I have the following formula where you see C5(Joe):

=IF(AND(C2=A5,G1=B5),B5,"")

And in C11 I have a totals forumla:

=COUNTIF(C5:C10,G1)

It is working, however with the amount of data I am calculating it causes considerable slow down.

I've been experimenting with the countif function.
IE:=COUNTIF(A5:A10,AND(C2=A5,C5=G1))
Which has not been working.

Any thoughts or suggestions on how to streamline this / clean it up would be appreciated.

Can someone help me? I need the data from a different file but don't want to have all linked files open every time i work on this File. Help!

******** ******************** ************************************************************************>Microsoft Excel - option1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF5G5H5F6G6H6F7G7H7#VALUE!Construction Costs 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

hello Friends,
I am having to write a vba code for a survey data captured code online.
I am attaching the code and the vba module that is suupose to do following things
2) dELETES THE LAST 3 COLUMNS
3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)
4)Inserts new row at top from Q1 TO Q11
5) Sorts data on Q6 (Faculty/Staff)
Before all this the i need to insert blank cells for
questions that have not been answered and shift
the cells right (condition 1)

condition 1)
For example file has data like
q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14
q1 q2 q6 q8
q1 q2 q7 q8 q10

code shod inpsert empty cells and resulting data should be formated lik
q1 q2 q3 q4 q5 q6 q7 q8 q9 q10 q11 q12 q13 q14
q1 q2 q6 q8
q1 q2 q7 q8 q10

I am attaching the file with 2 worksheets vba macro-code "Pres"
To test code open Sheet12 and run macro -Module10.Pres
rsults meet conditions 2-5
I have been able to meet conditions 2-5 after manually inserting blanks
and putting it in sequention order. As shown in Sheet11
Sheet1 has unsorted data that needs to be put in sequential order
could someone help me with writing vba code to meet 1st condition

File:http://204.56.128.173/test/SurveyTest.xls


No luck finding an answer? You could always try Google.