A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Setting up and Configuration of Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to increment sheets in an excel formula with auto fill



 
 
Thread Tools Display Modes
  #1  
Old March 14th 13, 10:24 PM
RC2902 RC2902 is offline
Junior Member
 
First recorded activity by ExcelBanter: Mar 2013
Posts: 1
Smile how to increment sheets in an excel formula with auto fill

Hi there,
Hoping someone may be able to assist me with my issue, I just can't workout what to do here.

I have a Workbook with approximately 30 sheets all with different names, for example:
Sheet1 is named "2013", Sheet2 named "DAY 1", Sheet3 "DAY2"...

On the sheet named "2013" in cell B4, I wish to reference a cell F27 on the sheet named "DAY 1". Then I wish to be able to auto fill across the page so that the referenced cell remains the same, however the sheet increments to "DAY2", then "DAY3" and so on.

So far I have used
='DAY 1'!$F$27

However the issue is, that the sheet does not increment from 'DAY 1' to DAY 2' then 'DAY 3' respectively with auto fill. I am able to manually change the entry from 'DAY 1' to 'DAY 2', however I may as well jut manually enter value from cell F27 on each sheet. Surely there is a formula to automate this?

Does some one have a solution to my problem? I have never used INDIRECT function, and have tried to apply different examples, however always results in a #REF error.

Assistance is greatly appreciated

Last edited by RC2902 : March 14th 13 at 10:33 PM. Reason: More info
Ads
  #2  
Old March 15th 13, 04:59 PM posted to microsoft.public.excel.setup
Gord Dibben[_2_]
external usenet poster
 
Posts: 623
Default how to increment sheets in an excel formula with auto fill

Use INDIRECT function

=INDIRECT("DAY" & (COLUMN()-1) & "!F27")

entered in B4 then dragged across

Gord


On Thu, 14 Mar 2013 22:24:35 +0000, RC2902
> wrote:

>
>Hi there,
>Hoping someone may be able to assist me with my issue, I just can't
>workout what to do here.
>
>I have a Workbook with approximately 30 sheets all with different names,
>for example:
>Sheet1 is named "2013", Sheet2 named "DAY 1", Sheet3 "DAY2"...
>
>On the sheet named "2013" in cell B4, I wish to reference a cell F27 on
>the sheet named "DAY 1". Then I wish to be able to auto fill across the
>page so that the referenced cell remains the same, however the sheet
>increments to "DAY2", then "DAY3" and so on.
>
>So far I have used
>='DAY 1'!$F$27
>
>However the issue is, that the sheet does not increment from 'DAY 1' to
>DAY 2' then 'DAY 3' respectively with auto fill. I am able to manually
>change the entry from 'DAY 1' to 'DAY 2', however I may as well jut
>manually enter value from cell F27 on each sheet. Surely there is a
>formula to automate this?
>
>Does some one have a solution to my problem? I have never used INDIRECT
>function, and have tried to apply different examples, however always
>results in a #REF error.
>
>Assistance is greatly appreciated

  #3  
Old December 8th 16, 01:27 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 1
Default how to increment sheets in an excel formula with auto fill

On Friday, March 15, 2013 at 12:59:19 PM UTC-4, Gord Dibben wrote:
> Use INDIRECT function
>
> =INDIRECT("DAY" & (COLUMN()-1) & "!F27")
>
> entered in B4 then dragged across
>
> Gord
>
>
> On Thu, 14 Mar 2013 22:24:35 +0000, RC2902
> > wrote:
>
> >
> >Hi there,
> >Hoping someone may be able to assist me with my issue, I just can't
> >workout what to do here.
> >
> >I have a Workbook with approximately 30 sheets all with different names,
> >for example:
> >Sheet1 is named "2013", Sheet2 named "DAY 1", Sheet3 "DAY2"...
> >
> >On the sheet named "2013" in cell B4, I wish to reference a cell F27 on
> >the sheet named "DAY 1". Then I wish to be able to auto fill across the
> >page so that the referenced cell remains the same, however the sheet
> >increments to "DAY2", then "DAY3" and so on.
> >
> >So far I have used
> >='DAY 1'!$F$27
> >
> >However the issue is, that the sheet does not increment from 'DAY 1' to
> >DAY 2' then 'DAY 3' respectively with auto fill. I am able to manually
> >change the entry from 'DAY 1' to 'DAY 2', however I may as well jut
> >manually enter value from cell F27 on each sheet. Surely there is a
> >formula to automate this?
> >
> >Does some one have a solution to my problem? I have never used INDIRECT
> >function, and have tried to apply different examples, however always
> >results in a #REF error.
> >
> >Assistance is greatly appreciated


Is there a way to use the INDIRECT function to increase a reference incrementally when a new sheet is created?

Like a cell in Sheet 1 references SheetMainA4, have the same cell in Sheet 2 reference SheetMain B4 etc?

I need to
  #4  
Old December 9th 16, 09:52 AM
PhuNuViet PhuNuViet is offline
Junior Member
 
First recorded activity by ExcelBanter: Dec 2016
Posts: 1
Default

Theo nghiên cứu của nhiều chuyên gia, với mỗi t*nh cách khác nhau, phụ nữ có t*nh cách khác nhau nên ham muốn tình dục khác nhau
Về cơ bản, mức độ ham muốn tình dục của phụ nữ có 4 cấp độ sau
<iframe allowfullscreen="" frameborder="0" height="315" src="https://www.youtube.com/watch?v=Tna66SQMqHo" width="560"></iframe>
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Increment formula jfcby Excel Programming 4 April 21st 09 05:29 PM
Auto fill Sheets Diogo Excel Programming 1 October 17th 08 11:43 AM
source sheet to auto fill 2 other sheets GetSFX New Users to Excel 0 August 8th 08 03:49 PM
increment an integer in a formula with the fill function vexed Excel Worksheet Functions 3 June 6th 08 05:20 PM
how to auto increment cell location within formula Bill Excel Worksheet Functions 3 February 16th 06 02:23 PM


All times are GMT +1. The time now is 03:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.