Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use cell to determine column in formula
Hi, I have a worksheet in which I add a new column each month for the new
activity in the trial balance. that information is in one sheet. In separate sheets, I have cash flow statements (one for each month, meaning 12 separate sheets). At this point I manually transfer totals from the trial balance to the cash flow statements. I would like to find a way to do the following: If I could have all the formulas on the cash flow statement point to the proper row on the trial balance BUT be dependent upon a variable for their column selection (columns Jan-Dec), then I could actually have just two sheets in this workbook... The monthly cash flow sheet would work for Jan-Dec by just changing that variable, which could possibly be the numbers 1-12, or something along those lines. Thanks in advance, any help would be GREATLY appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use cell to determine column in formula
Hi Jeff
On your TB sheet set up a named range. InsertNameDefineName TB Refers to =$A$1:$M$100 I assume you have 13 columns, with column A being the heading. Extend the number of rows to cover the range of your data. On your Cash Flow sheet, assuming headings again in column A, and values in column B Enter in B1 the Month number required In the first row of data, say row 3 enter in B3 =INDEX(TB,Row(),$B$1+1) Copy down as far as required -- Regards Roger Govier "JeffC" wrote in message ... Hi, I have a worksheet in which I add a new column each month for the new activity in the trial balance. that information is in one sheet. In separate sheets, I have cash flow statements (one for each month, meaning 12 separate sheets). At this point I manually transfer totals from the trial balance to the cash flow statements. I would like to find a way to do the following: If I could have all the formulas on the cash flow statement point to the proper row on the trial balance BUT be dependent upon a variable for their column selection (columns Jan-Dec), then I could actually have just two sheets in this workbook... The monthly cash flow sheet would work for Jan-Dec by just changing that variable, which could possibly be the numbers 1-12, or something along those lines. Thanks in advance, any help would be GREATLY appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use cell to determine column in formula
Either use Match() function to identify the column number
=MATCH("<month",A1:L1,0) OR If the month columns are fixed you can get the column number using the below function by passing the date. A1 = date =TEXT(A1,"m") -- If this post helps click Yes --------------- Jacob Skaria "JeffC" wrote: Hi, I have a worksheet in which I add a new column each month for the new activity in the trial balance. that information is in one sheet. In separate sheets, I have cash flow statements (one for each month, meaning 12 separate sheets). At this point I manually transfer totals from the trial balance to the cash flow statements. I would like to find a way to do the following: If I could have all the formulas on the cash flow statement point to the proper row on the trial balance BUT be dependent upon a variable for their column selection (columns Jan-Dec), then I could actually have just two sheets in this workbook... The monthly cash flow sheet would work for Jan-Dec by just changing that variable, which could possibly be the numbers 1-12, or something along those lines. Thanks in advance, any help would be GREATLY appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I determine the last cell in a Column | Excel Discussion (Misc queries) | |||
How to determine the column letter from Cell address | Excel Worksheet Functions | |||
How can I determine the largest physical size cell in a column | Excel Worksheet Functions | |||
Formula to determine row of hi value filtered column | Excel Discussion (Misc queries) | |||
formula to determine the first column containing any data | Excel Worksheet Functions |