Related Posts
Visual Storyteller
Bowl Leader
Thoughts on fooda?
More Posts
Hi Fishes,
How much can I ask from BNY Mellon for the role of Lead Full-Stack Engineer.
My Tech stack: Java, Spring Boot, Microservices, React, Node and AWS
YOE: 7.5 years
CCTC: 23.71L all fixed. Have received MVP bonus of 1.6L EY Tata Consultancy Deloitte Accenture PwC ZS Associates PwC
Please do comment and put your thoughts.
Pro
Make gwap and pine tings
Additional Posts in Excel Genius
Anyone know what these badges are?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Throw it all in a folder, go to data, merge queries from folder, and boom
Your can use formula bot to give you ideas on how to do this and more. It's an ai tool.
One way to do that, is to list the names of forms you have in one column, and in the next column collect the info using INDIRECT (excel) or IMPORTRANGE (google sheets)
For example assuming that data is in “[form1.xls]sheet1!B2” where form1 is the name of the file stored in column A
You can write in column B
=INDIRECT(“[“& A1 &”.xls]sheet1!B2”)
There are other ways of course, and some with VBA/google script app, but simpler is generally better
being very much impressed by all the nonsense of the other answers, I wrote down how this would work with one formula only with Excel.
VBA. it’s easy to write a loop to iterate all sheets, or all workbooks, to get that cell and combine it into one list.
Otherwise, the suggestion to use Power Query, or use Power BI, makes sense.
I have used chatGPT to help write VBA and it works wonders just need to be specific about what you want it to do.
You can prompt an ai to write it for you, but that might be beyond your skill, but still you can prompt an Ai. I asked Gemini the following:
- In VBA, write code to loop through each workbook in a folder, collect the value in cell B2, and at it to a list.
It produced this:
Sub CollectB2Values()
' Define the folder path (replace with your actual path)
Const FLDR_PATH As String = "C:\Your\Folder\Path\"
' Declare variables
Dim strFile As String
Dim wbk As Workbook
Dim dataList As Collection
Dim rw As Long
' Create a collection to store the values
Set dataList = New Collection
' Turn off screen updating for faster execution
Application.ScreenUpdating = False
' Loop through each file in the folder (*.xls* includes both xlsx and xlsm)
strFile = Dir(FLDR_PATH & "*.xls*")
Do While strFile <> ""
' Set rw to 2 (starting position for the list)
rw = 2
' Open the workbook in read-only mode
On Error Resume Next
Set wbk = Workbooks.Open(Filename:=FLDR_PATH & strFile, ReadOnly:=True)
' Check if workbook opened successfully
If Not wbk Is Nothing Then
' Get the value from cell B2 of the first worksheet
dataList.Add wbk.Worksheets(1).Range("B2").Value
' Close the workbook without saving changes
wbk.Close SaveChanges:=False
End If
On Error GoTo 0 ' Reset error handling
' Get the next file in the folder
strFile = Dir
Loop
' Add the collected values to a new column (replace "Sheet1" with your desired sheet)
With ThisWorkbook.Worksheets("Sheet1")
.Range("A" & rw & ":A" & dataList.Count + 1).Value = dataList.ToArray
End With
' Turn screen updating back on
Application.ScreenUpdating = True
' Inform user about completion
MsgBox "Values from B2 collected successfully!", vbInformation
End Sub