indirect address with skip - formula

[bluepenink]

im not to familiar with the indirect and skipping rows and applying it into a formula.

my formula is as follows or reference

='J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[New York.xls]G.S Branch'!$D$6

-the period/week starts on column C7, and after every 21 rows a new week starts, so I would like the italic "P1W1" to sequentially look off that cell ...keep in mind it skips every 21 cells.

-also, the city named can be locked down to $C$5.

can anyone help me with incorporating an indirect/skipping function or one that seems feasible to execute it in the formula; so it's referencing to the correct cell? PLS AND THX YOU very much!
again,
the period/week skips 21 rows so P1W1 is in C7; P1W2 is in C28 etc all the way to P12W5 ....

-also, the city name can be locked to C5 as it can be referenced to that.

so practically, i want the P1W1 to change when after every 21 rows, and for all other items to remain constant (i.e. the city can be looked from $C$5). thx you very much.

 

[alx7000]

Hey,

Hopefully you can use the below formula to get what you want, put it in D7 and copy down to see what it does but basically it takes the value 1 cell to the left every 21 rows, otherwise takes the value above:
=IF(MOD((ROW()-ROW($C$7)),21)=0,C7,D6)

Any questions please let me know.

Cheers,
alx7000

 

[bluepenink]

hello alex, thxs for your help

just wondering how i can incorporate this into my formula?

='J:\Sales Analyst\SWAT\Archieve 2011\(IF(MOD((ROW()-ROW($C$7)),21)=0,C7,D6)\[$C$5$.xls]G.S Branch'!$D$6

when i do this, bc its a link that is going into the cell, excel is asking me to manually find the file; is there a way for excel to automatically pick it up?

basically the above formula should translate into excel like this

='J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[Atlanta-Charleston.xls]G.S Branch'!$D$6

when i manually link the cell to the file, the reference works, but i have like 24k cells and it'll be a pain to it al manually....am i doing something wrong? pls help and thx u

 

[alx7000]

Hey,

I think you'll have to use an indirect function, try:
=indirect("'J:\Sales Analyst\SWAT\Archieve 2011\"&(IF(MOD((ROW()-ROW($C$7)),21)=0,C7,D6)&"\["&$C$5$&".xls]G.S Branch'!$D$6)"

May take quite awhile to update if you have lots of data points, INDIRECT is a volatile function so likes recalculating itself fairly regularly as well.

If you only need to make these links once, might be quicker to run a macro. Let me know how you get on.

Cheers,
alx7000

 

[bluepenink]

alex,

i will definitely try it when i can connect to the server first thing in the morning. also, i did think about a macro (see below)

Option Explicit
 
Sub CreateFormulas()
Dim ws       As Worksheet
Dim Rw       As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
 
For Each ws In Worksheets
    If ws.Range("C5") = "Period" Then
        For Rw = 7 To 1119 Step 21
            With ws.Range("C" & Rw + 1, "Y" & Rw + 20)
                .Replace "
[*]", "[" & ws.Name & ".xls]", LookAt:=xlPart
                .Replace "\P*\", "\" & Range("C" & Rw) & "\", LookAt:=xlPart
            End With
        Next Rw
    End If
Next ws
 
Application.ScreenUpdating = True
End
 
[\code]
 
but the issue i face here is....im not that super at them and for some apparent reason, i cant get, if u recall from the formula, the cell reference is $E$5 linking to another sheet....well i cant get the last part to stay as it is on the sheet, when i run the macro, it makes it relative and thus, puts everything out of order.
 
im not sure how i can code it so the last part stays absolute.....so thas why i came here to seek some help that maybe deriving it from a formula might work?...ur thoughts, and thx you very much!

 

[bluepenink]

hey alex,

so i took off the quotation mark "" in the formula to make it look like this

=INDIRECT('J:\Sales Analyst\SWAT\Archieve 2011\"&(IF(MOD((ROW()-ROW($C$7)),21)=0,C7,D6)&"\["&$C$6&".xls]G.S Branch'!$D5)

it's working better in terms of, it's trying to look for a file, but for apparent reason, it's giving an error, saying it cant find the path, or the file is in use or it shares the same name as my workbook - all which are untrue.

any ideas? thxs

 

[alx7000]

Hey,

You definitely need quotes around it for the indirect function to work but I haven't used it much for looking up cells in other workbooks for for so not sure how to troubleshoot that sorry.

However, the following method might work, it's what I've used previously when trying to import data from multiple filepaths where there is a pattern in the location/filename:

1. Create a formula that when 'pasted as values' will look up the correct cell
2. Copy formula down and paste as values
3. Run a macro over cells to make excel recognise them as formulas rather than text

So, for step 1 I think the formula you will need is:
="='J:\Sales Analyst\SWAT\Archieve 2011\"&(IF(MOD((ROW()-ROW($C$7)),21)=0,C7,D6))&"\["&$C$5&".xls]G.S Branch'!$D$6)"

Paste the formula into a cell, then paste the cell as a value, then F2 and enter to evaluate it.

The macro is:

Sub Ftwoplusenter()
Dim c As Range
For Each c In Range("e1:e200").Cells
c.Select
SendKeys "{F2}", True
SendKeys "{ENTER}", True
Next
End Sub

You will need to change e1:e200 to the range you require.

Be careful that before you run the macro the formulas have correct filepaths in them, otherwise for each cell excel will open a box for you to browse to the correct file location! If that happens I normally kill excel through the task manager and find what filepath was incorrect.

I realise this is getting a bit complex and detailed sorry, I'm just not sure what else to do. Let me know how you get on!

Cheers,
alx7000

 

[bluepenink]

not sure if im doing it right, but for some reason, when i do it, this is the end result?

='J:\Sales Analyst\SWAT\Archieve 2011\\[Period.xls]G.S Branch'!$D$6

it totally skips out on the P1W1 part, and places Period.xls instead of the city name?

this is what it should resemble?
='J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[Miami-Ft Myers.xls]G.S Branch'!$F$5....

ur thoughts, alex? not sure where i might be messing up