IMPORTRANGE not working in Excel - can anyone help pls?

T

tcjotm

New Member
Joined
May 7, 2018
Messages
7
Hello!

I have been using google sheets but I now want to use Excel instead. I am using Excel 2010 with Win 8.1 and Chrome all on desktop.

I had a formula made for me to display some data in a certain way, but it doesn't seem to work in Excel. Can anyone help with the Excel version please?

I have uploaded the example xlsx to my google drive - link is here if you wish to download to make it easier : https://drive.google.com/file/d/1RJgNbFZtTsXJnNS3AawVK3qpGq2v3Aq-/view?usp=sharing

Sheet 1 is my raw data.
Sheet 2 is a paste of my current google sheets formula, minus the first '=' so it displays as text and not a broken formula.
Sheet 3 is what it sheet 1 looks like on google sheets after being processed by the formula.

The formula should be fairly self-explanatory but in summary :

1. Columns C to J are the only ones of relevance from sheet 1 - ignore the data in all the other columns, including column D.
2. Data from column C should be displayed without the letter suffix.
3. If Column J contains a '>' symbol, display a 'D' on sheet3.
4. If Column H contains EHRD, show DEP in column B on sheet3.
5. If Column I contains EHRD, show ARR in column B on sheet3.
6. If both columns H and I contain EHRD, show RT in column B on sheet3.

For those members not wishing to download the xlsx, here is the google sheet formula which pulls from a source google sheet :

Code: 
[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]{[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"TIME"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"STS"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"CALLSIGN"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"TYPE"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"REG"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"DIV"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata];[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]REGEXREPLACE[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col1"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]".\z"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col6"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col7"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRDEHRD"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"RT"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col6"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"DEP"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col7"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"ARR"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col3, Col4, Col5"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]ArrayFormula[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]if[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Query[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]importrange[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"https://docs.google.com/spreadsheets/d/1a8xduxzUwoioGkKWF-35b_kJoWIRsZCDz9doaHuaAks/edit#gid=1521280153"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"EHRD!C1:J"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Select Col8"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]">"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"D"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]}[/FONT][/COLOR]

Is this possible to convert to Excel usage whereby the docs.google link is my Excel sheet1 and I wish for the 'end product' to be displayed on sheet2 of the same work book?

I would be extremely grateful to any kind person who can make some sense of this for me. I understand basic functions but I have to be honest, array formulas are well beyond my knowledge scope.

Thank you and rgds.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
NoSparks said:
Any interest in a macro to accomplish this ?
Click to expand...

Hi Sparks.

I've never used Macros before and wouldn't know what to do with one or where to place it, but if you believe that is the best solution and can help me "install" it then sure, I would gladly welcome your assistance. Thanks.
 
Upvote 0
Where to paste the code ? These sites will be of assistance.
https://www.rondebruin.nl/win/code.htm
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/
http://www.contextures.com/xlvba01.html

Code: 
Option Explicit

Sub MrExcel_tcjotm()
    
    Dim lr As Long, r As Long
    Dim rng As Range, cel As Range
    Dim str As String

With Sheets("Sheet1")
    'determine last row used in col C
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    'set the range to work with
    Set rng = .Range("C1:C" & lr)
End With

With Sheets("Sheet2")
    'the row to start on
    r = 6
    'put in headers
    .Range("A" & r).Resize(, 6).Value = Split("TIME STS CALLSIGN TYPE REG DIV")
    
    'work with each cell in rng
    For Each cel In rng
        'increment row
        r = r + 1
        'first col is the time
        str = str & "|" & Left(cel.Value, Len(cel.Value) - 1)
        
        'second col depends on values in H and I
        If cel.Offset(, 5) = "EHRD" And cel.Offset(, 6) = "EHRD" Then
            str = str & "|" & "RT"
        ElseIf cel.Offset(, 5) = "EHRD" And cel.Offset(, 6) <> "EHRD" Then
            str = str & "|" & "DEP"
        ElseIf cel.Offset(, 5) <> "EHRD" And cel.Offset(, 6) = "EHRD" Then
            str = str & "|" & "ARR"
        Else
            str = str & "|"
        End If
        
        'third, forth and fifth col is col E-G
        str = str & "|" & cel.Offset(, 2).Value & "|" & cel.Offset(, 3).Value & "|" & cel.Offset(, 4).Value
        
        'sixth col
        If cel.Offset(, 7).Value = ">" Then
            str = str & "|" & "D"
        Else
            str = str & "|"
        End If
        
        'write this data to the sheet
        .Range("A" & r).Resize(, 6).Value = Split(Mid(str, 2), "|")
        
        'reset str
        str = ""
    Next cel
End With
End Sub
 
Upvote 0
NoSparks said:
Where to paste the code ? These sites will be of assistance.
https://www.rondebruin.nl/win/code.htm
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/
http://www.contextures.com/xlvba01.html

Code: 
Option Explicit

Sub MrExcel_tcjotm()
    
    Dim lr As Long, r As Long
    Dim rng As Range, cel As Range
    Dim str As String

With Sheets("Sheet1")
    'determine last row used in col C
    lr = .Range("C" & Rows.Count).End(xlUp).Row
    'set the range to work with
    Set rng = .Range("C1:C" & lr)
End With

With Sheets("Sheet2")
    'the row to start on
    r = 6
    'put in headers
    .Range("A" & r).Resize(, 6).Value = Split("TIME STS CALLSIGN TYPE REG DIV")
    
    'work with each cell in rng
    For Each cel In rng
        'increment row
        r = r + 1
        'first col is the time
        str = str & "|" & Left(cel.Value, Len(cel.Value) - 1)
        
        'second col depends on values in H and I
        If cel.Offset(, 5) = "EHRD" And cel.Offset(, 6) = "EHRD" Then
            str = str & "|" & "RT"
        ElseIf cel.Offset(, 5) = "EHRD" And cel.Offset(, 6) <> "EHRD" Then
            str = str & "|" & "DEP"
        ElseIf cel.Offset(, 5) <> "EHRD" And cel.Offset(, 6) = "EHRD" Then
            str = str & "|" & "ARR"
        Else
            str = str & "|"
        End If
        
        'third, forth and fifth col is col E-G
        str = str & "|" & cel.Offset(, 2).Value & "|" & cel.Offset(, 3).Value & "|" & cel.Offset(, 4).Value
        
        'sixth col
        If cel.Offset(, 7).Value = ">" Then
            str = str & "|" & "D"
        Else
            str = str & "|"
        End If
        
        'write this data to the sheet
        .Range("A" & r).Resize(, 6).Value = Split(Mid(str, 2), "|")
        
        'reset str
        str = ""
    Next cel
End With
End Sub
Click to expand...

Thanks Sparks! That's a lot of code!

I am having problems pasting it. I am following the first link and have pasted it on the top 'workbook' link at the top of the menu tree and done 'insert module' and pasted in the code but when I try to save it it gives me an error message "the following feature cannot be saved in an macro free workbook : VB project". If I click no it opens up my file explorer and I'm not sure whether to click yes to save it as a 'macro free workbook'. I've never used these functions before so I haven't got a clue what I'm doing tbh.
 
Upvote 0
Disregard above! I figured it out! Works great - thanks a lot ! :)

One last question if I may : I have will numerous data sets like this. What do I need to change to add the additional data sets ? For example : currently the 'raw' data is on sheet1 and the 'edited' version is on sheet2 after running ALT F8 VBA formula. If I were to paste another set of raw data on sheet3, what would I need to do the run the same VBA formula to produce an edited version of sheet3 on sheet4?
 
Upvote 0
tcjotm said:
Disregard above! I figured it out! Works great - thanks a lot ! :)

One last question if I may : I have will numerous data sets like this. What do I need to change to add the additional data sets ? For example : currently the 'raw' data is on sheet1 and the 'edited' version is on sheet2 after running ALT F8 VBA formula. If I were to paste another set of raw data on sheet3, what would I need to do the run the same VBA formula to produce an edited version of sheet3 on sheet4?
Click to expand...

OK I figured this out now! :)

My problem is now : I have 2 separate macro modules for my 2 sheets. When I click 'run' it only runs the first macro in the list. I will have approx 30 sheets in total and so it is ridiculous to have to run each of the 30 modules manually. Is there a setting which will run them all at once? I don't see it in the 'options' panel.

Also, when I change the data on the 'input' sheet and run the macro again it only overwrites the current cells and doesn't clear the sheet beforehand. This causes me a problem as my previous data set may have been 200 rows long but the new data set is only 100 rows long, so I end up with rows 101 to 200 still showing at the bottom of the sheet. I know that I can highlight the sheet and select 'delete' to clear the contents first, but this is too time-consuming for 30 sheets. Is there a setting somewhere that will wipe the sheets first (I don't have any conditional formatting or anything that would cause an interference) and then run all the modules at once to 'pull' the latest data from the source sheets? Even better would be if this could automated when it detects changes to the source sheets so I don't have to run the macro modules manually. Is this possible to do or is it beyond the limitations of Excel ?
 
Upvote 0
Multiple macros are not necessary.
Easy to put the single macro into a loop to deal with the desired sheets.
So question is... what are the names of (or how to identify) which sheets to process?
and what are the names (or how to identify) the sheets to put the results in?

Also, when I change the data on the 'input' sheet and run the macro again it only overwrites the current cells and doesn't clear the sheet beforehand.
Click to expand...
Was this requirement previously mentioned?
It could be as simple as having just UsedRange.Clearcontents in the macro.

Even better would be if this could automated when it detects changes to the source sheets so I don't have to run the macro modules manually. Is this possible to do or is it beyond the limitations of Excel ?
Click to expand...
That depends.... how do you obtain the raw data? how do you change source data?
 
Upvote 0
Would you be so kind as to post the solution so others (including myself) may have the opportunity to learn from it. Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Help with a formula.
Replies
1
Views
100
Rijnsent
Rijnsent
D
  • Question
I'm looking for a formula.
Replies
2
Views
114
devinberry1997
D
R
  • Question
anyone help me with a ecuations in avanced filters pls
Replies
0
Views
300
RIXXZ2
R
A
  • Question
Solver Optimization Tool
Replies
1
Views
121
Eric W
Eric W
R
  • Question
Merge Excel Multiple Sheets from a single work book to one sheet.
2
Replies
11
Views
320
rtr1811
R

Forum statistics

Threads
1,216,112
Messages
6,128,901
Members
449,477
Latest member
panjongshing

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top