Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing the value of a cell by one per worksheet?
Hello all,
I'm new to excel programming. I'm working with a large workbook (over 200 sheets) and I need to input a number into the same cell (F1) in every sheet. I need sheet 001 to display "1" (no quotes) in F1, and sheet 002 to display "2", 003 to display 3, all the way to sheet 200 displaying '200'. What formula can I use to achieve this output? Another thing I need to do is come up with a function that takes a number value in a cell in one sheet (one of the sheets named 001, 002, 003, etc.) and places it in a specific cell in, let's say, the "INDEX" sheet. But I need the output cell in the "INDEX" sheet to be different for every 001 002 003 004 sheet. Let's say I need the number from cell A1 from every sheet, but I need each sheet to display their respective A1's in a different cell in the INDEX sheet. Yeah, it's confusing me. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing the value of a cell by one per worksheet?
" wrote: Hello all, I'm new to excel programming. I'm working with a large workbook (over 200 sheets) and I need to input a number into the same cell (F1) in every sheet. I need sheet 001 to display "1" (no quotes) in F1, and sheet 002 to display "2", 003 to display 3, all the way to sheet 200 displaying '200'. What formula can I use to achieve this output? Another thing I need to do is come up with a function that takes a number value in a cell in one sheet (one of the sheets named 001, 002, 003, etc.) and places it in a specific cell in, let's say, the "INDEX" sheet. But I need the output cell in the "INDEX" sheet to be different for every 001 002 003 004 sheet. Let's say I need the number from cell A1 from every sheet, but I need each sheet to display their respective A1's in a different cell in the INDEX sheet. Yeah, it's confusing me. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing the value of a cell by one per worksheet?
Sub ABC()
Dim i as long, s as String, sh as Worksheet for i = 1 to 200 s = format(i,"000") set sh = Worksheets(s) sh.Range("F1").Value = i worksheets("Index").Cells(i,1) = sh.Range("A1").Value Next End Sub -- Regards, Tom Ogilvy wrote in message ups.com... Hello all, I'm new to excel programming. I'm working with a large workbook (over 200 sheets) and I need to input a number into the same cell (F1) in every sheet. I need sheet 001 to display "1" (no quotes) in F1, and sheet 002 to display "2", 003 to display 3, all the way to sheet 200 displaying '200'. What formula can I use to achieve this output? Another thing I need to do is come up with a function that takes a number value in a cell in one sheet (one of the sheets named 001, 002, 003, etc.) and places it in a specific cell in, let's say, the "INDEX" sheet. But I need the output cell in the "INDEX" sheet to be different for every 001 002 003 004 sheet. Let's say I need the number from cell A1 from every sheet, but I need each sheet to display their respective A1's in a different cell in the INDEX sheet. Yeah, it's confusing me. Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing the value of a cell by one per worksheet?
If you're talking about macros you could do this:-
For a = 1 To 200 Worksheets(a).Range("F1") = a Next for the first part of your request and For a = 1 To 200 Cells(a + 1, 1) = Worksheets(a).Range("A1") Next should list the values in cell A1 for each sheet. " wrote: Hello all, I'm new to excel programming. I'm working with a large workbook (over 200 sheets) and I need to input a number into the same cell (F1) in every sheet. I need sheet 001 to display "1" (no quotes) in F1, and sheet 002 to display "2", 003 to display 3, all the way to sheet 200 displaying '200'. What formula can I use to achieve this output? Another thing I need to do is come up with a function that takes a number value in a cell in one sheet (one of the sheets named 001, 002, 003, etc.) and places it in a specific cell in, let's say, the "INDEX" sheet. But I need the output cell in the "INDEX" sheet to be different for every 001 002 003 004 sheet. Let's say I need the number from cell A1 from every sheet, but I need each sheet to display their respective A1's in a different cell in the INDEX sheet. Yeah, it's confusing me. Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increasing the value of a cell by one per worksheet?
You should look closely at solutions that loop
for each sh in worksheet or for i = 1 to worksheets.count type loops. if you have a worksheet named Index, then if it is at the front, all the numbers will be off by 1 and you will process the index sheet as well. Just a thought. I suppose you could do for each sh in worksheets if lcase(sh.name) < "index" then sh.Range("F1").Value = clng(sh.name) end if worksheets("Index").cells(clng(sh.name _ ),"B").Value = sh.Range("A1").Value next -- Regards, Tom Ogilvy wrote in message ups.com... Hello all, I'm new to excel programming. I'm working with a large workbook (over 200 sheets) and I need to input a number into the same cell (F1) in every sheet. I need sheet 001 to display "1" (no quotes) in F1, and sheet 002 to display "2", 003 to display 3, all the way to sheet 200 displaying '200'. What formula can I use to achieve this output? Another thing I need to do is come up with a function that takes a number value in a cell in one sheet (one of the sheets named 001, 002, 003, etc.) and places it in a specific cell in, let's say, the "INDEX" sheet. But I need the output cell in the "INDEX" sheet to be different for every 001 002 003 004 sheet. Let's say I need the number from cell A1 from every sheet, but I need each sheet to display their respective A1's in a different cell in the INDEX sheet. Yeah, it's confusing me. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY DOWN INCREASING WORKSHEET REFERENCE BY 1 | Excel Worksheet Functions | |||
How do I copy a formula with increasing worksheet numbers | Excel Discussion (Misc queries) | |||
Increasing the date/time in cells on a worksheet with a button | Excel Worksheet Functions | |||
Code Increasing Size of Worksheet | Excel Programming | |||
increasing the number of rows of a worksheet beyond 65536? | Excel Discussion (Misc queries) |