UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Retrieve Data From Other Worksheet, Office 2013    
 
   
Biplab1985
post Mar 20 2018, 12:09 PM
Post#1



Posts: 153
Joined: 18-August 16
From: Bangalore


Hi Guys,

I have a workbook attached, 1. Data Entry sheet and 2. Master Data Sheet.

2 purpose of the template
1. To Do data entry and move data to Master Data Worksheet - This Part is already done(Copyto Master command button)
2. To retrieve saved Data in Master Data worksheet 0- Help needed

Thanks!
Biplab
Attached File(s)
Attached File  To_Test.zip ( 21.09K )Number of downloads: 6
Attached File  To_Test.zip ( 21.09K )Number of downloads: 2
 
Go to the top of the page
 
ADezii
post Mar 20 2018, 02:38 PM
Post#2



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


I am little confused as to your request, a little clarification should do it.
  1. Do you wish to retrieve Data from the Output Worksheet in To_Test.xlsm?
  2. Once the Data has been retrieved, what would you like to do with it?
Go to the top of the page
 
Biplab1985
post Mar 20 2018, 03:46 PM
Post#3



Posts: 153
Joined: 18-August 16
From: Bangalore


Hi,

1. Yes, Retrieve data from Output Worksheet to Input worksheet on Selection of store1 in A2 cell and by clicking the Retrieve Data button. Condition would be Quater, Week, Apple/Carrot/Pineapple.
I have already Coded to move the Data from Input To output worksheet but next step is to get back the data when needed from output back to input.

2. The process is user enter the data manually by selecting store and enter values in column E through P of each quater(Q1,Q2,Q3 and Q4). It will create a data dump in output sheet(a sample data is
already there) by appending.
User will also retrieve back the data when needed to see the numbers,produce report and take decision out of it. Just pulling back the data to input sheet.

Hoped this explains.

Thanks!
This post has been edited by Biplab1985: Mar 20 2018, 03:49 PM
Go to the top of the page
 
ADezii
post Mar 20 2018, 05:27 PM
Post#4



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


It would be helpful to see actual Data in the Input Sheet prior to it being moved.
Go to the top of the page
 
Biplab1985
post Mar 21 2018, 02:25 AM
Post#5



Posts: 153
Joined: 18-August 16
From: Bangalore


Attached is how the input data looks like for Store 2 of Q1.

Thanks!

Attached File(s)
Attached File  To_Test.zip ( 23.16K )Number of downloads: 4
 
Go to the top of the page
 
Biplab1985
post Mar 21 2018, 09:18 AM
Post#6



Posts: 153
Joined: 18-August 16
From: Bangalore


Guys,

Anyone could help me out here? Much appreciated. I just need the data back to the inPut sheet from output based on Selection
Go to the top of the page
 
Biplab1985
post Mar 21 2018, 01:10 PM
Post#7



Posts: 153
Joined: 18-August 16
From: Bangalore


Guys,

can anyone please help me out here. i have tried to code but had no luck, have deadline so need to close it at the earliest.
Thanks in advance
This post has been edited by Biplab1985: Mar 21 2018, 01:28 PM
Go to the top of the page
 
Biplab1985
post Mar 22 2018, 08:15 AM
Post#8



Posts: 153
Joined: 18-August 16
From: Bangalore


Experts,

I have given all the information that was asked from me. Thought of getting some input to start but did not receive any help
Go to the top of the page
 
ADezii
post Mar 22 2018, 11:13 AM
Post#9



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


To reverse the Output Logic (Retrieve) is a little more involved that I thought, at least for me. I am very pressed for time so what I threw together was a Brute-Force approach to re-populate the Input Sheet based on a specified Quarter (Q1). It is probably not what you are looking for but hopefully will point you in the right direction. When I have more time, I will look into this further, but for now this is all that I can come up with.
Attached File(s)
Attached File  To_Test_Revised.zip ( 30.51K )Number of downloads: 5
 
Go to the top of the page
 
Biplab1985
post Mar 22 2018, 04:41 PM
Post#10



Posts: 153
Joined: 18-August 16
From: Bangalore


Thank you for the code, Instead of fixing the Quater to Q1, can i make it dynamic. I tried with a range function but getting error. If you help me out here i think i can do the rest.

I am getting error in the last row

CODE
Set strQtr = wksInp.Range("C6:C18")

''Do While wks.Cells(intRow, "E") <> ""
''

For Each cel In strQtr.Cells
With cel
  If wks.Cells(intRow, "B") = cel Then

Go to the top of the page
 
ADezii
post Mar 23 2018, 09:24 AM
Post#11



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


I am not sure if I understand you correctly, but it appears as though you are trying to set a String Variable to an Excel Range Object which won`t work. Try something like:
CODE
Dim rngQtr As Excel.Range
Dim rngCellInRange As Excel.Range

Set rngQtr = wksInp.Range("C6:C18")

For Each rngCellInRange In rngQtr
   If wks.Cells(intRow, "B") = rngCellInRange Then
      `processing here
   End If
Next
Go to the top of the page
 
Biplab1985
post Mar 27 2018, 05:31 AM
Post#12



Posts: 153
Joined: 18-August 16
From: Bangalore


I was actually referring to my original requirement.

I am still not getting the output for Q2. I tried using loop by referencing Q1, Q2,Q3 and Q4 but looks like it is not the right approach.

Any suggestion
Go to the top of the page
 
ADezii
post Mar 27 2018, 12:49 PM
Post#13



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Try placing the Quarters into an Array, then Loop thru the Array in a Nesting Structure:
    CODE
    Dim varQtr As Variant
    Dim intCtr As Integer

    varQtr = Array("Q1", "Q2", "Q3", "Q4")

    For intCtr = LBound(varQtr) To UBound(varQtr)
      Debug.Print varQtr(intCtr)
    Next
  2. OUTPUT:
    CODE
    Q1
    Q2
    Q3
    Q4
Go to the top of the page
 
Biplab1985
post Mar 28 2018, 05:04 PM
Post#14



Posts: 153
Joined: 18-August 16
From: Bangalore


It Did not work. I used Index and Match Function to retrieve back the data
This post has been edited by Biplab1985: Mar 28 2018, 05:04 PM
Go to the top of the page
 
dflak
post Mar 29 2018, 11:20 AM
Post#15


Utter Access VIP
Posts: 6,289
Joined: 22-June 04
From: North Carolina


See if this gets you one step closer.

First of all, I recommend converting the data (Output) into an Excel Table simply because they are easier to manipulate. Also I added a helper column called Composite which is a concatenation of key fields that uniquely define the record. I use this for the Match part of Match / Index.

Secondly, I recommend only copying over data where there is data to copy over so there are no blank records. I can help with that.

What I can arrange is to have you select the customer. This will fire off the formulas in the For Recovery sheet (which can be hidden in the final version).

Then copy the results into the Input Sheet.

Does this sound like it would work for you?
Attached File(s)
Attached File  To_Test_2.zip ( 35.33K )Number of downloads: 4
 
Go to the top of the page
 
Biplab1985
post Apr 1 2018, 12:45 PM
Post#16



Posts: 153
Joined: 18-August 16
From: Bangalore


Hi Dflak,

That sounds pretty much what i wanted. All i needed is to avoid using formulas to retrieve back the data.

Thanks for your help on this
Go to the top of the page
 
dflak
post Apr 2 2018, 12:37 PM
Post#17


Utter Access VIP
Posts: 6,289
Joined: 22-June 04
From: North Carolina


I *am* using formulas on the For Recovery sheet. My proposition is to copy them over as values only.

It looks like you are trying to do something more suitable to a database. What do you expect to happen when you change data for a store? How do you plan to find the old record and update it?

I can envision a scheme where when you save the data, it saves each record with a composite unique key like I have with the table. When you fill out the form and click on save, it deletes all the records in the database for that store and replaces with the new records. When you retrieve the data, it simply pulls the information from the database similar to what I am doing with the attached.

I suppose I should ask if the number of rows and columns in the display are fixed. I can't see where you can add weeks, but you may wind up extending across Apple, Carrot and Pineapple. Do you plan to do this?


Attached File(s)
Attached File  To_Test_2.zip ( 36.84K )Number of downloads: 6
 
Go to the top of the page
 
Biplab1985
post Feb 25 2020, 12:28 PM
Post#18



Posts: 153
Joined: 18-August 16
From: Bangalore


Thank you so much Dflak notworthy.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 07:51 PM