pasting depend upon value

Posted by Bevan on May 15, 2001 3:49 PM

Hello,

i am trying to paste data to different places dependant upon a value in a certain cell (date). i have two spreadsheets: daily report and monthly, i need to copy from the daily (easily done as it is always the same format) and then depending upon the contents of one cell (in the daily report) paste it to the monthly spreadsheet in that days cells. can anyone offer a suggestion to help my macro out? thanks

Posted by Kevin James on May 15, 2001 6:54 PM

Hi Bevan,

What you are asking would require more than a simple recordable macro. It would require VBA (Visual Basic for Applications).

Another alternative is to take a lookup at the VLOOKUP function in Excel. Based on the generalalities you posed in your message, the syntax would be something to the effect:

=vlookup([RequiredValue],[RangeInsideDailyReport],[ColumnToReturn])

Example:

The value you are testing for is 10, based on the cell (I think you said) in the monthly report.

VLOOKUP will go out to the daily report, look for value 10 in range of columns you specify, and, if found, return the value for the column you specify.

If the daily and monthly reports has the following

Column A is the match-value column
Column B is store number
Column C is EOD revenue
Column D is whatever

then the VLOOKUP in the monthly report would be:

B2:
Vlookup(A2,Daily!A:D,2)
C2:
Vlookup(A2,Daily!A:D,3)
D2:
Vlookup(A2,Daily!A:D,4)
E2:
Vlookup(A2,Daily!A:D,5)

You would then copy the formula down for each cell in the columns.

Afterwards, to "freeze" the numbers, you can convert then from formulas to value-only.

See your Excel help files for further explanation.

Kevin