Excel: Kjede sammen og Indirekte

[HKRoed]

Hei!

Har ett Excelark med oversikt over anbud, Anbud.xls.

Alle anbud er også en egen fil, feks 1156.xls. Alle filer ligger i samme mappe

 

I kolonne A står navnet på anbudet, feks 1156. Jeg prøver å få hentet inn innholdet fra B3 i 1156.xls til kolonne B i Anbud.xls.

Har testet med Kjede sammen-funksjonen, men da viser den bare formelen. Har også funnet ut at jeg må bruke Indirekte-funksjonen, men den får jeg ikke til å fungere.

Står bare #REF i cellen...

 

Prøver denne formelen: =INDIREKTE(KJEDE.SAMMEN(L143;M143;A143;K2;N143;O143))

som gir denne adressen: =[1156.xls]Ark1!$D$3

Noe som egentlig er riktig. Men jeg ønsker å fylle hele kolonne B med denne formelen, og da forandres ikke filnavnet.

 

Kan noen hjelpe meg? :-)

Endret 30. august 2015 av SpritHansi

[Harald Staff]

Hei

 

Som du har oppdaget virker ikke INDIREKTE mot lukkede arbeidsbøker. En formel som denne

=[1156.xls]Ark1!$D$3

vil endre seg til noe a la dette

='C:\Temp\[1156.xls]Ark1'!$D$3

idet du lukker kildefilen.

 

Det er meg bekjent ingen formelløsning på dette, du må i gang med en eller annen form for VBA-koding, eventuelt strukturere dataene dine annerledes. Her er en løsning

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

men googler du get data from closed workbook finner du mange alternative måter å gjøre det på.

 

Beste hilsen Harald

[HKRoed]

Ja, det har jeg oppdaget... :-(

Så inntil videre blir det copy-paste. Er over 2000 filer der jeg ønsker data fra 3 celler. Til nå har det vært dobbelbokføring, og det tror jeg det fortsetter med :-P

 

Men takk for svar (Y)

[ExcelGuru]

Hei på deg.

Utfordringen din er at formelen ikke endrer navn når du kopierer den nedover i B-kolonnen.

I dette eksemplet har jeg skrevet en liten snutt som bytter ut filnavnet i formelen ut fra anbudsnummeret i A-kolonnen

Du kopierer først formelen helt ned, og så kjører du makroen.

 

Eksemplet finner du her

http://www.excelguru.no/download/1450/

 

(Hvis du har de tre filene 1156.xlsx, 1157.xlsx og 1158.xlsx på samme område, funker eksemplet fint)

 

Makroen ser slik ut:

Sub Knapp1_Klikk()

 Dim Anbud As String

 Dim Formel As String

 Dim x As Integer

 Dim a As Integer

 Dim b As Integer

 Dim FilType As String

 Dim AnbudsNummer_Kolonne As Integer

 Dim Formel_Kolonne As Integer

 

 'Disse tre parametrene må du muligens endre...

 FilType = ".xlsx"

 AnbudsNummer_Kolonne = 1

 Formel_Kolonne = 2

 

 x = 1: While Cells(x, AnbudsNummer_Kolonne) <> ""

  Anbud = Cells(x, AnbudsNummer_Kolonne)

  Formel = Cells(x, Formel_Kolonne).Formula

  

  

  a = InStr(1, Formel, "[")

  b = InStr(1, Formel, "]")

  

  If a <> 0 And b <> 0 Then

     Formel = Left(Formel, a) & Anbud & FilType & Mid(Formel, b)

     Cells(x, Formel_Kolonne).Formula = Formel

  End If

 x = x + 1: Wend

End Sub

[HKRoed]

Oi. Det var snilt. Tusen takk :-)

Skal teste litt utover nattan, og gi deg tilbakemelding ?

[HKRoed]

Ser ut som denne fungerer helt genialt :-)

Men jeg ønsker å hente ut data fra 3 stk celler.

Tipper det er denne linja jeg må endre til noe??

Formel_Kolonne = 2

[ExcelGuru]

Ser ut som denne fungerer helt genialt :-)

Men jeg ønsker å hente ut data fra 3 stk celler.

Tipper det er denne linja jeg må endre til noe??

Formel_Kolonne = 2

 

Så fint at det virker. Ja, det er Formel_Kolonne som bestemmer hvilken kolonne formelen som skal endres ligger i. Er dette en engangsjobb funker vel det greit, ellers kan jeg mekke litt til så den endrer på tre kolonner i en smekk

 

Formel_Kolonne=2 er B-kolonnen, Formel_Kolonne=3 er C-kolonnen osv

 

Vennlig hilsen Ketil

Endret 2. september 2015 av ExcelGuru

[HKRoed]

Nei, har fått ordnet det.

Kopierte opp scriptet ditt, men endret fra formel til formel2, formel3, og fra Anbud til Anbud2 og Anbud 3.

Så igjen, takk :-)

[HKRoed]

Men, det jeg kunne tenkt meg, var en mulighet for å forhåndsdefinere hvilket ark dataene er i.

Det er ALLTID ark1 jeg ønsker å hente fra, men i de fleste filene er det 3 ark. Da må jeg bekrefte hvert ark...

[Henrik C]

Sheet(1).Cell(.....) skal vel fikse den biffen greit? Eller er det Sheets(1)? Husker aldri syntaxen.

 

Ellers hatten av til ExcelGuru for en veldig galant makro!

[HKRoed]

Sheet(1).Cell(.....) skal vel fikse den biffen greit? Eller er det Sheets(1)? Husker aldri syntaxen.

 

Ellers hatten av til ExcelGuru for en veldig galant makro!

Men hvor skal dette skrives inn?

[Henrik C]

x = 1: While Cells(x, AnbudsNummer_Kolonne) <> ""

  Anbud = Sheets(1).Cells(x, AnbudsNummer_Kolonne)

  Formel = Cells(x, Formel_Kolonne).Formula

 

Sitter på en Mac her og får ikke testet, men er rimelig sikker på at den skal ordne biffen. 

[HKRoed]

 

x = 1: While Cells(x, AnbudsNummer_Kolonne) <> ""

  Anbud = Sheets(1).Cells(x, AnbudsNummer_Kolonne)

  Formel = Cells(x, Formel_Kolonne).Formula

 

Sitter på en Mac her og får ikke testet, men er rimelig sikker på at den skal ordne biffen. 

 

Beklager, det gikk ikke. Da fant den ikke anbudsnummeret i A-kolonna, så alle celler ble #REF